The SQLite substr() function allows you to return a substring from a string, based on a given starting location within the string.

It requires two arguments, and accepts a third optional argument.

Update: As from SQLite 3.34.0 (released on 1st December 2020), substr() can now be called substring() for compatibility with SQL Server.

Syntax

You can call substr() in any of the following ways:

substr(X,Y,Z)
substr(X,Y)
substring(X,Y,Z)
substring(X,Y)

• X is the full string that contains the substring you want to return.
• Y is the location of the first character of substring that you want to return from that string.
• Z is the number of characters that you want returned. If omitted, all subsequent characters are returned (starting from Y).

If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

Note that the substring() syntax is only available from SQLite 3.34.0.

Example with 3 Arguments

Here’s a basic example to demonstrate how to use substr() with three arguments.

SELECT substr('Industrial', 3, 4);

Result:

dust

Example with 2 Arguments

If I omit the third argument from the previous example I get the following result.

SELECT substr('Industrial', 3);

Result:

dustrial

Negative Starting Point

You can provide a negative value for the second argument. When you do this, the first character of the substring is found by counting from the right rather than the left.

SELECT substr('Industrial', -3);

Result:

ial

Here’s another example, this time I specify the length of the substring.

SELECT substr('Industrial', -8, 4);

Result:

dust

So negative values have the same effect as when using the substring() function in MySQL. MySQL also has a substr() function, which is a synonym of its substring() function.

However, this is different to how the SQL Server treats negative values. When you pass a negative value to substring() in SQL Server, it will simply start counting at an imaginary point before the string begins.

Negative Substring Length

The previous example used a negative starting point. In this example, I’m going to use a negative length. By this I mean I’m going to provide a negative value for the third argument.

SELECT substr('Industrial', -3, -4);

Result:

ustr

So providing a negative value for the third argument results in the characters preceding the starting point being returned.

This also applies when the second argument is a positive value.

SELECT substr('Industrial', 7, -4);

Result:

dust

Database Example

Here’s an example that uses substr() in a database query against the Chinook sample database.

SELECT substr(Title, 1, 20)
FROM Album
LIMIT 10;

Result:

substr(Title, 1, 20)
--------------------
For Those About To R
Balls to the Wall   
Restless and Wild   
Let There Be Rock   
Big Ones            
Jagged Little Pill  
Facelift            
Warner 25 Anos      
Plays Metallica By F
Audioslave