Core SQL functions

The following table describes the supported core SQL functions.

Function	Description
abs(X)	Returns the absolute value of X. Returns NULL if X is NULL, 0.0 if X is a non-numeric string or blob.
changes()	Returns the number of rows affected by the most recent INSERT, UPDATE, or DELETE statement.
char(X1, X2, ..., XN)	Constructs a string from Unicode code points X1 to XN.
coalesce(X, Y, ...)	Returns the first non-null argument.
concat(X, ...)	Concatenates all non-null arguments into a single string.
concat_ws(SEP, X, ...)	Concatenates non-null arguments using SEP as a separator.
format(FORMAT, ...)	Formats a string based on the FORMAT template and subsequent arguments.
glob(X, Y)	Evaluates whether string X matches pattern Y using the GLOB operator, which is equivalent to Y GLOB X.
hex(X)	Interprets X as a BLOB and returns its upper-case hexadecimal string representation. A number is first converted to UTF-8 text before being interpreted as a BLOB.
if(B1, V1, ...)	Returns V1 for the first true B1; otherwise, evaluates the next pair or returns the last value if all are false.
ifnull(X, Y)	Returns Y if X is NULL; otherwise, returns X.
iif(B1, V1, V2)	Returns V1 if B1 is true; otherwise, returns V2.
instr(X, Y)	Returns the first occurrence of Y first within X counting from 1 (not 0), or 0 if not found. Returns NULL if either argument is NULL.
last_insert_rowid()	Returns the ROWID of the last row inserted from the current database connection. Returns NULL if no row has been inserted on the current connection.
length(X)	Returns the number of characters in a string X, or bytes in a BLOB X. For strings: returns the character or code-point length of the string (not bytes). Returns NULL if X is NULL. For numeric X: returns length of string representation.
like(X, Y)	Implements the Y LIKE X expression. X is the pattern, and Y is the string to match. When no ESCAPE clause is present, the function is called with two arguments. Parameters are reversed compared to the infix LIKE operator.
like(X,Y,Z)	Implements Y LIKE X ESCAPE Z. X is the pattern, Y is the string to match, and Z is the escape character. If the ESCAPE clause is present, the function is called with three arguments; otherwise, it uses two.
likelihood(X, Y)	Assumes X is true with a probability of Y.
likely(X)	Returns X unchanged but hints to the query planner that X is usually true (equivalent to likelihood(X,0.9375)).
lower(X)	Converts string X to lowercase.
ltrim(X)	Removes spaces from the left side of string X.
ltrim(X, Y)	Removes any characters that appear in Y from the left side of string X.
max(X, Y, ...)	Returns the maximum value among the arguments. If any argument is NULL, returns NULL.
min(X, Y, ...)	Returns the minimum value among the arguments.
nullif(X, Y)	Returns NULL if X equals Y; otherwise, returns X.
octet_length(X)	Returns the length of string X in bytes.
printf(FORMAT, ...)	Formats a string based on the FORMAT template and subsequent arguments.
quote(X)	Returns a value formatted as a SQL literal, ready for inclusion in SQL statements. Strings are enclosed in single quotes, with internal quotes escaped as needed. All BLOB values are hex-encoded. Strings containing NUL characters are truncated at the first NUL.
random()	Generates a pseudo-random integer in the range -9223372036854775808 to +9223372036854775807.
randomblob(N)	Returns an N-byte BLOB of pseudo-random data. If N is less than 1, then a 1-byte random, BLOB is returned.
replace(X, Y, Z)	Replaces all occurrences of Y in X with Z (case-sensitive). If Y is empty, returns X unchanged.
round(X, Y)	Rounds X to Y digits to the right of the decimal point.
rtrim(X)	Removes spaces from the right side of string X.
rtrim(X, Y)	Removes any characters that appear in Y from the right side of string X.
sign(X)	Returns -1, 0, or +1 if X is negative, zero, or positive respectively. Returns NULL if X is NULL or a string or BLOB that cannot be losslessly converted to a number.
soundex(X)	Returns the soundex encoding of string X. Returns ?000 if X is NULL or contains no ASCII alphabetic characters.
substr(X, Y)	Returns a substring of X starting at position Y (1-indexed) through the end of the string. If Y is negative, counting starts from the end of the string rather than the beginning.
substr(X, Y, Z)	Returns a substring of X starting with the Y-th character (1-indexed) and which is Z characters long. If Y is negative, counting starts from the end of the string rather than the beginning.
substring(X, Y)	Alias for substr(X, Y).
substring(X, Y, Z)	Alias for substr(X, Y, Z).
total_changes()	Returns the total number of rows modified by INSERT, UPDATE or DELETE since the database connection was opened.
trim(X)	Removes spaces from both ends of string X.
trim(X, Y)	Removes any characters that appear in Y from both ends of string X.
typeof(X)	Returns the data type of X as a string: null, integer, real, text, or blob.
unhex(X)	Returns a BLOB decoded from the hexadecimal string X. X must be a pure hexadecimal string with uninterrupted pairs of digits; otherwise, NULL is returned. The case of hexadecimal digits in X does not affect decoding. Returns NULL if X is NULL.
unhex(X, Y)	Returns a BLOB decoded from the hexadecimal string X. If X contains characters that are not hexadecimal digits and are not in Y, NULL is returned. X must consist of uninterrupted pairs of hexadecimal digits; otherwise, unhex(X,Y) returns NULL. Also, returns NULL if either X or Y is NULL.
unicode(X)	Returns the numeric Unicode code point of the first character in string X. Result is undefined if X is not a string.
unlikely(X)	Returns X unchanged. Hints to the query planner that X is usually a false boolean expression. Equivalent to likelihood(X, 0.0625).
upper(X)	Returns a copy of string X with all lowercase ASCII characters converted to their uppercase equivalent.
zeroblob(N)	Returns a BLOB consisting of N bytes of 0x00. Can be used to reserve space for a BLOB that is written later.