In PostgreSQL you can use the extract() function to get the year from a date.
You can also use the date_part() function to do the same thing.
Author: Ian
3 Ways to Backup an SQLite Database
When 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:
- Use the
.backupcommand to back up a specified database - Use the
.dumpcommand to export the database to a .sql file - Use the
.clonecommand to clone the database
2 Ways to Insert a New Line into a String in SQLite
This 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 readingHow SQLite Char() Works
In 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 readingHow to Concatenate Strings in SQLite
SQLite 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.
Get the Position of a Character in a String in SQLite with Instr()
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 readingHow last_insert_rowid() Works in SQLite
SQLite has a function called last_insert_rowid() that returns the ROWID of the last row insert from the database connection which invoked the function.
How SQLite Length() Works
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 readingHow 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.
Continue readingHow 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.