The SQLite length() function returns the number of characters in a string, number, or blob.
If there are any NUL characters, it returns the number of characters before the first NUL character.
How SQLite Quote() Works
The SQLite quote() function allows you to escape a string so that it’s suitable for inclusion in an SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes.
BLOBs are encoded as hexadecimal literals.
Note that strings with embedded NUL characters cannot be represented as string literals in SQL. If you include strings with embedded NUL characters, the returned string literal is truncated prior to the first NUL.
How to Generate a Random Number within a Specified Range in SQLite
In SQLite, you can use the random() function to generate a pseudo-random number.
This is great, but the value returned is between -9223372036854775808 and +9223372036854775807.
What if you need a random number between 0 and 10? Or say, 1 and 100?
Fortunately you can do this by combining random() with abs() and the modulo operator.
Difference Between NullIf() and IfNull() in SQLite
SQLite has a function called nullif() and another function called ifnull(), each of which serve a different purpose.
nullif()allows you to treat certain values as NULL. You can think of it as “return NULL if …”.ifnull()allows you to replace NULL values with another value. You can think of it as “if NULL, then …”.
So they basically do the opposite of each other. One replaces NULL values with another value, and the other replaces another value with NULL.
How Replace() Works in SQLite
The SQLite replace() function enables us to replace one string (or part of a string) with another string.
The way it works is that you provide three arguments; the string that contains the substring to replace, the substring within that string to replace, and the string to replace it with.
Find Foreign Key Violations in SQLite
SQLite includes a PRAGMA statement that allows you to check for foreign key violations on a whole database or a given table.
The statement is PRAGMA foreign_key_check, and it works as follows.
Return a List of Foreign Keys in SQLite
In SQLite, you can use a PRAGMA statement to return a list of foreign keys for a given table.