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.

Name	Description
SUBSTR	Extract and returns a substring with a predefined length starting at a specified position in a source string
TRIM	Return a copy of a string that has 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 another 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 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;

1	SELECT INSTR('SQLite String Functions','Functions') Position;

`LENGTH`

SELECT 
    LENGTH('SQLite');

1 2	SELECT LENGTH('SQLite');

`LOWER`

SELECT
    LOWER('String Functions');

1 2	SELECT LOWER('String Functions');

`LTRIM`

SELECT 
   LTRIM(' SQLite '),
   LENGTH(' SQLite ') LengthBefore,
   LENGTH(LTRIM(' SQLite ')) LengthAfter;

SELECT

LTRIM(' SQLite '),

LENGTH(' SQLite ') LengthBefore,

LENGTH(LTRIM(' SQLite ')) LengthAfter;

`RTRIM`

SELECT
   RTRIM(' SQLite '),
   LENGTH(' SQLite ') LengthBefore,
   LENGTH(RTRIM(' SQLite ')) LengthAfter;

SELECT

RTRIM(' SQLite '),

LENGTH(' SQLite ') LengthBefore,

LENGTH(RTRIM(' SQLite ')) LengthAfter;

`REPLACE`

SELECT 
    REPLACE('These are string functions', 'These', 'Those');

1 2	SELECT REPLACE('These are string functions', 'These', 'Those');

`SUBSTR`

SELECT 
    SUBSTR('SQLite String Functions', 1, 6);

1 2	SELECT SUBSTR('SQLite String Functions', 1, 6);

`TRIM`

SELECT 
    TRIM(' SQLite ');

1 2	SELECT TRIM(' SQLite ');

`UPPER`

SELECT
    UPPER('String Functions');

1 2	SELECT UPPER('String Functions');

Concatenation Operator ||

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

SELECT 'Concatenation ' || 'Operator';

1	SELECT 'Concatenation ' \|\| 'Operator';

Was this tutorial helpful ?
Yes No