Viewing: www.sqlitetutorial.net

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone

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

Name	Description
SUBSTR	Extract and return 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 the characters converted to lowercase.
INSTR	Find a substring in a string and return an integer indicating the position of the first occurrence of the substring.
Concatenation Operator \|\|	Concatenate multiple strings into a single string.
CONCAT_WS	Concatenate multiple string into a single string using a specified separator

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]

CONCAT

The following statement uses the concat() function to concatenate multiple strings into a single string:

SELECT concat('Hello', ',', ' ', 'World','!') message;Code language: JavaScript (javascript)

Output:

message
-------------
Hello, World!

Was this tutorial helpful ?

SQLite String Functions