SQLite: substr Function

This SQLite post explains how to use the SQLite substr function with syntax and examples.

Description

The SQLite substr function allows you to extract a substring from a string.

Syntax

The syntax for the substr function in SQLite is:

substr( string, start_position, [ length ] )

Parameters or Arguments

string

The source string.

start_position

The position for extraction. The first position in the string is always 1.

length

Optional. It is the number of characters to extract. If this parameter is omitted, the substr function will return the entire remaining string.

Note

• The first position in string is 1.
• If start_position is a positive number, then the substr function starts from the beginning of the string.
• If start_position is a negative number, then the substr function starts from the end of the string and counts backwards.

Applies To

The substr function can be used in the following versions of SQLite:

• SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

Example

Let's look at some SQLite substr function examples and explore how to use the substr function in SQLite.

For example:

sqlite> SELECT substr('AODBA.com', 5);
        -> ' A.com  '

sqlite> SELECT substr('AODBA.com', 5, 2);
        -> ' A.'

sqlite> SELECT substr('AODBA.com', 1, 4);
        -> 'AODB'

sqlite> SELECT substr('AODBA.com', -3, 3);
        -> 'com'

sqlite> SELECT substr('AODBA.com', -3, 1);
        -> 'c'