SQLite Functions

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

NameDescription
AVGReturn the average value of non-null values in a group
COUNTReturn the total number of rows in a table.
MAXReturn the maximum value of all values in a group.
MINReturn the minimum value of all values in a group.
SUMReturn the sum of all non-null values in a column
GROUP_CONCATConcatenate non-null values in a column

SQLite string functions

NameDescription
SUBSTRExtract and return a substring with a predefined length starting at a specified position in a source string
TRIMReturn a copy of a string with specified characters removed from the beginning and the end of a string.
LTRIMReturn a copy of a string that has specified characters removed from the beginning of a string.
RTRIMReturn a copy of a string that has specified characters removed from the end of a string.
LENGTHReturn the number of characters in a string or the number of bytes in a BLOB.
REPLACEReturn a copy of a string with each instance of a substring replaced by the other substring.
UPPERReturn a copy of a string with all of the characters converted to uppercase.
LOWERReturn a copy of a string with all of the characters converted to lowercase.
INSTRFind a substring in a string and return an integer indicating the position of the first occurrence of the substring.

SQLite control flow functions

NameDescription
COALESCEReturn the first non-null argument
IFNULLProvide the NULL if/else construct
IIFAdd if-else logic to queries.
NULLIFReturn NULL if the first argument is equal to the second argument.

SQlite date and time functions

NameDescription
DATECalculate the date based on multiple date modifiers.
TIMECalculate the time based on multiple date modifiers.
DATETIMECalculate the date & time based on one or more date modifiers.
JULIANDAYReturn the Julian day, which is the number of days since noon in Greenwich on November 24, 4714 B.C.
STRFTIMEFormat the date based on a specified format string.

SQLite math functions

NameDescription
ABSReturn the absolute value of a number.
RANDOMReturn a random integer value.
ROUNDRound off a floating value to a specified precision.

For a complete list of math functions in SQLite, check this page.

Was this tutorial helpful ?