Sometimes you just want to see the basic structure of a table.
In SQLite, there are several ways to retrieve information about a given table. In particular, here are four options:
PRAGMA table_info() statementPRAGMA table_xinfo() statement (for virtual tables).schema commandsqlite_master tableExamples of each method are below.
Continue readingWhen it comes to backing up your databases in SQLite, you have a few options as to how to go about it.
In particular, you can use one of the following methods:
.backup command to back up a specified database.dump command to export the database to a .sql file.clone command to clone the databaseThis article presents two ways to insert a new line character into a string in SQLite.
This means you can have some text on one line, more text on another line, etc, rather than it being one long line.
Continue readingIn SQLite, the char() function returns a string based on the unicode code points provided as arguments.
You can provide one or more arguments, each of which is a unicode code point. The function then returns a string based on those code points.
Continue readingSQLite doesn’t have a concat() function like many other database management systems, such as SQL Server’s concat() and MySQL’s concat().
However, that doesn’t mean you can’t concatenate two strings in SQLite.
SQLite has a concatenation operator (||) that you can use to concatenate two strings.
In SQLite, you can use the instr() function to return the position of a given character within a string.
The function only returns the position of the first occurrence of the character (if any).
If the character isn’t found, then it returns 0.
If either of the arguments are NULL, then it returns NULL.
Continue readingSQLite has a function called last_insert_rowid() that returns the ROWID of the last row insert from the database connection which invoked the function.
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.
Continue readingThe 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.
Continue readingIn 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.