SQLite String Functions

The following table shows the commonly used SQLite string functions that perform an operation on an input string and return a new string or a numeric value.

NameDescription
SUBSTRExtract and returns a substring with a predefined length starting at a specified position in a source string
TRIMReturn a copy of a string that has 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 another 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 returns an integer indicating the position of the first occurrence of the substring.
Concatenation Operator ||Concatenate two strings into a single string.

 INSTR

SELECT INSTR('SQLite String Functions','Functions') Position;Code language: SQL (Structured Query Language) (sql)
SQLite string functions - INSTR function

 LENGTH

SELECT 
    LENGTH('SQLite');Code language: SQL (Structured Query Language) (sql)
SQLite string functions - LENGTH function

 LOWER

SELECT
    LOWER('String Functions');
Code language: SQL (Structured Query Language) (sql)
SQLite string functions - LOWER function

 LTRIM

SELECT 
   LTRIM(' SQLite '),
   LENGTH(' SQLite ') LengthBefore,
   LENGTH(LTRIM(' SQLite ')) LengthAfter;
Code language: SQL (Structured Query Language) (sql)
SQLite string functions - LTRIM function

 RTRIM

SELECT
   RTRIM(' SQLite '),
   LENGTH(' SQLite ') LengthBefore,
   LENGTH(RTRIM(' SQLite ')) LengthAfter;Code language: SQL (Structured Query Language) (sql)

 REPLACE

SELECT 
    REPLACE('These are string functions', 'These', 'Those');Code language: SQL (Structured Query Language) (sql)
SQLite string functions - REPLACE function

 SUBSTR

SELECT 
    SUBSTR('SQLite String Functions', 1, 6);
Code language: SQL (Structured Query Language) (sql)
SQLite string functions - SUBSTR function

 TRIM

SELECT 
    TRIM(' SQLite ');Code language: SQL (Structured Query Language) (sql)
SQLite string functions - TRIM function

 UPPER

SELECT
    UPPER('String Functions');Code language: SQL (Structured Query Language) (sql)
SQLite string functions - UPPER function

Concatenation Operator ||

This example shows how to concatenate two strings into a single string:

SELECT 'Concatenation ' || 'Operator';Code language: SQL (Structured Query Language) (sql)
SQLite string functions - concatenation operator example
Was this tutorial helpful ?