SQLite has many built-in functions. For easy reference, a list of all SQLite functions are provided. These functions can be used in queries in SQLite and can also be used within the programming environment provided by the SQLite database, such as SQL statements, triggers, etc.
Below is the list of SQLite functions, sorted by category:
SQLite Aggregate Functions
| Function | Description |
|---|
| AVG() | Returns the average value of an expression |
| COUNT() | Returns the number of records returned by a select query |
| GROUP_CONCAT() | Returns a string which is the concatenation of all non-NULL values |
| MAX() | Returns the maximum value in a set of values |
| MIN() | Returns the minimum value in a set of values |
| SUM() | Calculates the sum of a set of values |
| TOTAL() | Calculates the sum of a set of values |
SQLite String Functions
| Function | Description |
|---|
| CHAR() | Return the character for each integer passed |
| Concatenate with || | Returns the concatenated string |
| INSTR() | Returns the position of the first occurrence of a string in another string |
| LENGTH() | Returns the length of the specified argument |
| LOWER() | Converts a string to lowercase |
| LTRIM() | Removes leading spaces from a string |
| QUOTE() | Escape the argument for use in an statement |
| REPLACE() | Replaces all occurrences of a substring within a string, with a new substring |
| RTRIM() | Removes trailing spaces from a string |
| SUBSTR() | Extracts a substring from a string (starting at any position) |
| SUBSTRING() | Extracts a substring from a string (starting at any position) |
| TRIM() | Removes leading and trailing spaces from a string |
| UPPER() | Converts a string to uppercase |
SQLite Math/Numeric Functions
| Function | Description |
|---|
| ABS() | Returns the absolute value of a number |
| ACOS() | Returns the arc cosine of a number |
| ACOSH() | Returns the inverse hyperbolic cosine of a number |
| ASIN() | Returns the arc sine of a number |
| ASINH() | Returns the inverse hyperbolic sine of a number |
| ATAN() | Returns the arc tangent of a number |
| ATANH() | Returns the inverse hyperbolic tangent of a number |
| ATAN2() | Returns the arc tangent of two numbers |
| CEIL() | Returns the smallest integer value that is >= to a number |
| CEILING() | Returns the smallest integer value that is >= to a number |
| COS() | Returns the trigonometric cosine of an angle |
| COSH() | Returns the hyperbolic cosine of a value |
| DEGREES() | Converts a value in radians to degrees |
| EXP() | Returns e raised to the power of a specified number |
| FLOOR() | Returns the largest integer value that is <= to a number |
| LN() | Returns the natural logarithm of a number |
| LOG() | Returns the base-10 logarithm of a number, or the logarithm of a number to a specified base |
| LOG10() | Returns the base-10 logarithm of a number |
| LOG2() | Returns the base-2 logarithm of a number |
| MAX() | Returns the greatest value of the list of arguments |
| MIN() | Returns the smallest value of the list of arguments |
| MOD() | Returns the remainder of a number divided by another number |
| PI() | Returns the value of PI |
| POW() | Returns the value of a number raised to the power of another number |
| POWER() | Returns the value of a number raised to the power of another number |
| RADIANS() | Converts a degree value into radians |
| RANDOM() | Returns a random number |
| ROUND() | Rounds a number to a specified number of decimal places |
| SIGN() | Returns the sign of a number |
| SIN() | Returns the trigonometric sine of an angle |
| SINH() | Returns the hyperbolic sine of a value |
| SQRT() | Returns the square root of a number |
| TAN() | Returns the trigonometric tangent of an angle |
| TANH() | Returns the hyperbolic tangent of a value |
| TRUNC() | Truncates a number and returns the integer part of a number, rounding toward zero. |
SQLite Date/Time Functions
| Function | Description |
|---|
| DATE() | Returns the date as text in 'YYYY-MM-DD' format. |
| DATETIME() | Returns the date/time as text in 'YYYY-MM-DD HH:MM:SS' format. |
| JULIANDAY() | Returns the date as a Julian Day. |
| NOW() | Returns the current date and time |
| STRFTIME() | Returns the date formatted according to the specified format string |
| TIME() | Returns the current date and time. |
| UNIXEPOCH() | Returns the date as a unix timestamp. |
SQLite Advanced Functions
| Function | Description |
|---|
| CASE() | Goes through conditions and return a value when the first condition is met |
| CHANGES() | Returns the number of database rows changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement |
| COALESCE() | Returns the first non-null value in a list |
| FORMAT() | Returns the formatted string. |
| GLOB() | Searches for a specified pattern in a specified column. Performs case-sensitive search |
| HEX() | Returns a string containing hexadecimal representation of decimal or string value |
| IFNULL() | Return a specified value if the expression is NULL, otherwise return the expression. |
| IIF() | Returns a value if a condition is TRUE, or another value if a condition is FALSE |
| LAST_INSERT_ROWID() | Returns the ROWID of the last row inserted or updated in a table |
| LIKE() | Searches for a specified pattern in a specified column |
| NULLIF() | Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned |
| PRINTF() | Returns the formatted string. |
| RANDOMBLOB() | Returns an N-byte blob containing pseudo-random bytes |
| SOUNDEX() | Returns a soundex string |
| SQLITE_SOURCE_ID() | Returns a string that identifies the specific version of the source code that was used to build the SQLite library |
| SQLITE_VERSION() | Returns the current version of the SQLite database |
| TOTAL_CHANGES() | Returns the number of row changes caused by INSERT, UPDATE or DELETE statements |
| TYPEOF() | Returns a string indicating the datatype of the expression |
| UNICODE() | Returns the numeric unicode code point corresponding to the first character of the specified string |
| ZEROBLOB() | Returns a BLOB consisting of N bytes of 0x00 |
