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, 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.
If 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.
As with most things in SQLite, there’s more than one way to get a list of temporary tables in a database.
Here I present two ways to return temporary tables in SQLite.
Continue readingHere are two ways to return a list of tables in all attached databases in SQLite.
The first method returns all tables and views for all attached databases.
The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.
Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.
Continue readingThe SQLite avg() function returns the average value of all non-NULL values within a group.
It accepts one argument, which is the value or group of values.
Continue reading