In SQLite, substring() is an alias for substr().

It returns a substring from a string, based on a given starting location within the string. Two arguments are required, and a third optional argument is accepted.

The substring() naming was introduced in SQLite 3.34.0, which was released on 1st December 2020. The reason that the substring() syntax was introduced was for compatibility with SQL Server.

Syntax

So you can now call the function 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.

As mentioned, 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 substring() with three arguments.

SELECT substring('Dolemite', 3, 4);

Result:

lemi

Example with 2 Arguments

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

SELECT substring('Dolemite', 3);

Result:

lemite

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 substring('Dolemite', -3);

Result:

ite

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

SELECT substring('Dolemite', -7, 4);

Result:

olem

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 substring('Dolemite', -1, -4);

Result:

emit

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 substring('Dolemite', 6, -4);

Result:

olem

Database Example

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

SELECT 
    substring(Name, 1, 10),
    Name
FROM Artist
ORDER BY Name DESC
LIMIT 10;

Result:

substring(Name, 1, 10)  Name                             
----------------------  ---------------------------------
Zeca Pagod              Zeca Pagodinho                   
Youssou N'              Youssou N'Dour                   
Yo-Yo Ma                Yo-Yo Ma                         
Yehudi Men              Yehudi Menuhin                   
Xis                     Xis                              
Wilhelm Ke              Wilhelm Kempff                   
Whitesnake              Whitesnake                       
Vinícius E              Vinícius E Qurteto Em Cy         
Vinícius E              Vinícius E Odette Lara           
Vinícius D              Vinícius De Moraes & Baden Powell

In this case, I returned the first ten characters from the Name column. I also returned the full contents of the Name column in order to compare the results.