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:
sqlite> ...>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.
Continue readingBy 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.
Continue readingIn 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.
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).
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.
Continue readingIn 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.
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:
INTEGER PRIMARY KEY. 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.
Continue readingIf you know about the avg() function in SQLite, you’re probably aware that it returns the average of all non-NULL X within a group.
But did you know you can add the DISTINCT keyword to this function?
If you add the DISTINCT keyword, avg() will calculate its results based on distinct values only. This is essentially the same as removing duplicate values and then calculating the average on the remaining values.
In SQLite, the Sum() function accepts an optional DISTINCT keyword that enables you to add just the distinct values in the group. That is, it removes any duplicates from its calculation.
So if there are say, three rows that contain 10, only one of those rows will be included in the results.
Continue readingWhen using the count() function in SQLite, you might find yourself in the situation where you only want to count distinct values. That is, you don’t want duplicate values to be counted multiple times.