SQLite String Functions – Complete List with Examples

Positions in SQLite strings start at 1. Lengths count characters for text and bytes for BLOBs. upper(), lower() and LIKE only know the 26 ASCII letters unless the ICU extension is loaded.

Name	Description	Since
`UPPER()`	Return a copy of a string with ASCII letters converted to uppercase. Letters such as é are left unchanged.
`LOWER()`	Return a copy of a string with ASCII letters converted to lowercase. Handy for case-insensitive comparisons.
`LENGTH()`	Return the number of characters in a string, or the number of bytes in a BLOB. NULL gives NULL.
`OCTET_LENGTH()`	Return the number of bytes a string takes in the database encoding (usually UTF-8), not the number of characters.	3.43.0
`SUBSTR()`	Return part of a string starting at a 1-based position, with an optional length. Negative starts count from the end.
`INSTR()`	Return the 1-based position of the first occurrence of one string inside another, or 0 if it isn't found.	3.7.15
`REPLACE()`	Replace every occurrence of one string with another inside a string. The search is case-sensitive.
`TRIM()`	Remove spaces, or any characters you list, from both ends of a string. Spaces inside the string are kept.
`LTRIM()`	Remove spaces, or characters you list, from the start (left side) of a string only.
`RTRIM()`	Remove spaces, or characters you list, from the end (right side) of a string only.
`\|\|`	Join strings with the \|\| operator, the standard SQL way to concatenate. If either side is NULL, the result is NULL.
`CONCAT()`	Join any number of values into one string. Unlike \|\|, NULL arguments are skipped instead of making the result NULL.	3.44.0
`CONCAT_WS()`	Join values with a separator between them, skipping NULLs. The ws stands for with separator.	3.44.0
`FORMAT()`	Build a string from a template with printf-style placeholders such as %s, %d, %.2f and %,d. printf() is the old name.	3.8.3
`CHAR()`	Return a string made from Unicode code point numbers, such as char(72, 105) for 'Hi'. The reverse of unicode().	3.7.16
`UNICODE()`	Return the Unicode code point of the first character of a string, such as 65 for 'A'.	3.7.16
`HEX()`	Return the hexadecimal form of a value's bytes (its UTF-8 text for strings). Often used to display BLOBs.
`UNHEX()`	Decode a hexadecimal string into a BLOB, the reverse of hex(). Returns NULL if the text isn't valid hex.	3.41.0
`QUOTE()`	Return a value written as an SQL literal: text in single quotes with quotes doubled, BLOBs as X'..', NULL as NULL.
`LIKE`	Match a string against a pattern with % (any run of characters) and _ (one character). Case-insensitive for ASCII.
`GLOB`	Match a string against a Unix-style pattern with *, ? and [...]. Unlike LIKE, GLOB is case-sensitive.

Name

Description

Since

UPPER()

Return a copy of a string with ASCII letters converted to uppercase. Letters such as é are left unchanged.

LOWER()

Return a copy of a string with ASCII letters converted to lowercase. Handy for case-insensitive comparisons.

LENGTH()

Return the number of characters in a string, or the number of bytes in a BLOB. NULL gives NULL.

OCTET_LENGTH()

Return the number of bytes a string takes in the database encoding (usually UTF-8), not the number of characters.

3.43.0

SUBSTR()

Return part of a string starting at a 1-based position, with an optional length. Negative starts count from the end.

INSTR()

Return the 1-based position of the first occurrence of one string inside another, or 0 if it isn't found.

3.7.15

REPLACE()

Replace every occurrence of one string with another inside a string. The search is case-sensitive.

TRIM()

Remove spaces, or any characters you list, from both ends of a string. Spaces inside the string are kept.

LTRIM()

Remove spaces, or characters you list, from the start (left side) of a string only.

RTRIM()

Remove spaces, or characters you list, from the end (right side) of a string only.

||

Join strings with the || operator, the standard SQL way to concatenate. If either side is NULL, the result is NULL.

CONCAT()

Join any number of values into one string. Unlike ||, NULL arguments are skipped instead of making the result NULL.

3.44.0

CONCAT_WS()

Join values with a separator between them, skipping NULLs. The ws stands for with separator.

3.44.0

FORMAT()

Build a string from a template with printf-style placeholders such as %s, %d, %.2f and %,d. printf() is the old name.

3.8.3

CHAR()

Return a string made from Unicode code point numbers, such as char(72, 105) for 'Hi'. The reverse of unicode().

3.7.16

UNICODE()

Return the Unicode code point of the first character of a string, such as 65 for 'A'.

3.7.16

HEX()

Return the hexadecimal form of a value's bytes (its UTF-8 text for strings). Often used to display BLOBs.

UNHEX()

Decode a hexadecimal string into a BLOB, the reverse of hex(). Returns NULL if the text isn't valid hex.

3.41.0

QUOTE()

Return a value written as an SQL literal: text in single quotes with quotes doubled, BLOBs as X'..', NULL as NULL.

LIKE

Match a string against a pattern with % (any run of characters) and _ (one character). Case-insensitive for ASCII.

GLOB

Match a string against a Unix-style pattern with *, ? and [...]. Unlike LIKE, GLOB is case-sensitive.