Database.Guide – Page 333

How SQLite Trim() Works

May 28, 2020May 17, 2020 by Ian

The SQLite trim() function trims whitespace or other characters from both sides of a string.

The function returns a copy of the string that you provide as an argument, with either whitespace, or other characters as specified, trimmed from both sides.

How SQLite Rtrim() Works

May 28, 2020May 17, 2020 by Ian

In SQLite, the rtrim() function enables you to trim whitespace or other characters from the right part of a string.

It returns a copy of the string that you provide as an argument, with the right part trimmed of either whitespace, or other characters as specified.

How SQLite Ltrim() Works

May 28, 2020May 17, 2020 by Ian

The SQLite ltrim() function trims whitespace or other characters from the left of a string.

More precisely, it returns a copy of the string that you provide as an argument, with the left part trimmed of either whitespace, or other characters that you specify.

How to Change the Command Line Prompt in SQLite

May 28, 2020May 16, 2020 by Ian

If you’ve ever used the SQLite command line shell, you’re probably familiar with the default command line prompt. Actually, there are two prompts:

The default main prompt looks like this: sqlite>
The default continuation prompt looks like this: ...>

If you don’t like these prompts, you can always change them with the .prompt dot command.

This article provides a quick demonstration on how to change these prompts.

How to Make SQLite’s LIKE Operator Case-Sensitive

May 28, 2020May 15, 2020 by Ian

By default, the SQLite LIKE operator is case-insensitive for ASCII characters. This means it will match uppercase and lowercase characters, regardless of which case you use in your pattern.

However, there is a technique you can use to make it case-sensitive.

How the LIKE Operator Works in SQLite

May 28, 2020May 14, 2020 by Ian

In SQLite, you can use the LIKE operator in your queries to do a pattern matching comparison.

For example, you can add it to your WHERE clause in order to return only rows that match a given pattern.

However, adding it to the WHERE clause isn’t the only way you can use the LIKE operator. You can also use it to return a boolean value.

SQLite Like() Function with Examples

May 28, 2020May 14, 2020 by Ian

The SQLite like() function enables you to compare a string against a given pattern. You can also specify an escape character if required.

You can use the like() function as an alternative to the LIKE operator (which enables you to run the Y LIKE X [ESCAPE Z] expression).

3 Ways to Replace NULL with “N/A” in SQLite

May 28, 2020May 13, 2020 by Ian

In SQLite, if you need to replace NULL results with text such as “N/A”, “None”, or even the text “NULL”, you can use one of the three solutions below.

List all Indexes in an SQLite Database

August 5, 2024May 13, 2020 by Ian

In this article I outline two ways to return a list of indexes in an SQLite database.

The first (and most obvious) method is to use the .indexes dot command. The second method is to query the sql_master table.

How AUTOINCREMENT Works in SQLite

May 28, 2020May 12, 2020 by Ian

In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.

There are a couple of ways you can create an AUTOINCREMENT column:

You can create it implicitly when you define the column as INTEGER PRIMARY KEY.
You can create it explicitly with the AUTOINCREMENT keyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.

Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.

However, there are some subtle differences between how each method works.