This section provides the core SQLite functions including aggregate functions, date & time functions, string functions, and math functions.
SQLite Aggregate Functions
This tutorial shows you how to use the SQLite aggregate functions to find the maximum, minimum, average, sum, and count of a set of values.
SQLite Date Functions
This section provides you with SQLite date and time functions that help you manipulate datetime data effectively.
SQLite String Functions
This section shows the most commonly used SQLite string functions that help you manipulate character string data effectively.
SQLite Window Functions
SQLite window functions perform a calculation on a set of rows that are related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single result row.
SQLite aggregate functions
| Name | Description |
| AVG | Return the average value of non-null values in a group |
| COUNT | Return the total number of rows in a table. |
| MAX | Return the maximum value of all values in a group. |
| MIN | Return the minimum value of all values in a group. |
| SUM | Return the sum of all non-null values in a column |
| GROUP_CONCAT | Concatenate non-null values in a column |
SQLite string functions
| Name | Description |
| SUBSTR | Extract and return a substring with a predefined length starting at a specified position in a source string |
| TRIM | Return a copy of a string with specified characters removed from the beginning and the end of a string. |
| LTRIM | Return a copy of a string that has specified characters removed from the beginning of a string. |
| RTRIM | Return a copy of a string that has specified characters removed from the end of a string. |
| LENGTH | Return the number of characters in a string or the number of bytes in a BLOB. |
| REPLACE | Return a copy of a string with each instance of a substring replaced by the other substring. |
| UPPER | Return a copy of a string with all of the characters converted to uppercase. |
| LOWER | Return a copy of a string with all of the characters converted to lowercase. |
| INSTR | Find a substring in a string and return an integer indicating the position of the first occurrence of the substring. |
SQLite control flow functions
| Name | Description |
| COALESCE | Return the first non-null argument |
| IFNULL | Provide the NULL if/else construct |
| IIF | Add if-else logic to queries. |
| NULLIF | Return NULL if the first argument is equal to the second argument. |
SQlite date and time functions
| Name | Description |
| DATE | Calculate the date based on multiple date modifiers. |
| TIME | Calculate the time based on multiple date modifiers. |
| DATETIME | Calculate the date & time based on one or more date modifiers. |
| JULIANDAY | Return the Julian day, which is the number of days since noon in Greenwich on November 24, 4714 B.C. |
| STRFTIME | Format the date based on a specified format string. |
SQLite math functions
| Name | Description |
| ABS | Return the absolute value of a number. |
| RANDOM | Return a random integer value. |
| ROUND | Round off a floating value to a specified precision. |
For a complete list of math functions in SQLite, check this page.
Was this tutorial helpful ?
