SQLite substr() and substring(): Extract Part of a String

Ninad Pathak

October 7, 2026

Off-by-one results from substr() come from two rules: SQLite counts positions from 1, not 0, and a negative start counts from the end. In this article I’ll walk you through how substr() works on real strings, like pulling the domain out of an email address, and how to split text on a delimiter.

TLDR

substr(text, start, length) returns part of a string. Positions start at 1.
Leave out length to read to the end, and use a negative start to count from the right.

substring() is the same function, available since SQLite 3.34.
Combine substr() with instr() to split a string on a delimiter.

What is substr() in SQLite?

substr() is SQLite’s built-in function for taking part of a string. In the docs’ words, it “returns a substring of input string X that begins with the Y-th character and which is Z characters long” (substr()). substring() is another name for it: “‘substring()’ is an alias for ‘substr()’ beginning with SQLite version 3.34”.

SQLite substr() syntax

The function takes the text and a start position, plus an optional length:

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

Argument	Meaning
`X`	The text (or BLOB) to cut from
`Y`	The start position. The first character is 1; a negative value counts from the right
`Z`	How many characters to take. Leave it out to read to the end; a negative value takes characters before `Y`

How to extract part of a string

To show you how this works, I will run each form on a fixed string, so you can count the characters yourself.

Step 1: Take characters from a start position

With a length, you get that many characters. Without one, you get the rest of the string:

sqlite> SELECT substr('SQLite Docs', 1, 6) AS first6, substr('SQLite Docs', 8) AS from8, substring('SQLite Docs', 8, 4) AS alias;
first6  from8  alias
------  -----  -----
SQLite  Docs   Docs

Step 2: Count from the end with a negative start

“If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned” (substr()):

sqlite> SELECT substr('report-2026.csv', -3) AS last3, substr('report-2026.csv', -8, 4) AS year, substr('SQLite', 4, -2) AS before4;
last3  year  before4
-----  ----  -------
csv    2026  QL

Examples

These examples use substr() on table data.

Split a string on a delimiter with instr()

instr(X, Y) returns the position of Y in X, “or 0 if Y is nowhere found within X” (instr()). Use it to cut a string in two:

sqlite> CREATE TABLE settings (pair TEXT); INSERT INTO settings VALUES ('theme=dark'), ('lang=en'), ('timeout=30');
sqlite> SELECT pair, substr(pair, 1, instr(pair, '=') - 1) AS key, substr(pair, instr(pair, '=') + 1) AS value FROM settings;
pair        key      value
----------  -------  -----
theme=dark  theme    dark
lang=en     lang     en
timeout=30  timeout  30

Get a file extension, with CASE or iif()

Guard against a missing delimiter with CASE, or with iif(), which does the same in one call:

sqlite> CREATE TABLE files (path TEXT); INSERT INTO files VALUES ('docs/intro.md'), ('img/logo.png'), ('README');
sqlite> SELECT path, CASE WHEN instr(path, '.') > 0 THEN substr(path, instr(path, '.') + 1) END AS ext FROM files;
path           ext
-------------  ---
docs/intro.md  md
img/logo.png   png
README
sqlite> SELECT path, iif(instr(path, '.') > 0, substr(path, instr(path, '.') + 1), NULL) AS ext FROM files;
path           ext
-------------  ---
docs/intro.md  md
img/logo.png   png
README

Filter rows by a prefix

substr() works in WHERE clauses too:

sqlite> SELECT path FROM files WHERE substr(path, 1, 5) = 'docs/';
path
-------------
docs/intro.md

LIKE and GLOB can do the same prefix test, with differences. LIKE ignores case for ASCII letters, while substr() and GLOB don’t. GLOB uses * as its wildcard, not %:

sqlite> SELECT 'DOCS/intro.md' LIKE 'docs/%' AS like_ci, substr('DOCS/intro.md', 1, 5) = 'docs/' AS substr_cs, 'docs/intro.md' GLOB 'docs/*' AS glob_star, 'docs/intro.md' GLOB 'docs/%' AS glob_pct;
like_ci  substr_cs  glob_star  glob_pct
-------  ---------  ---------  --------
1        0          1          0

Edge cases

These are the results that look like bugs the first time you see them.

A start of 0 is off by one

Positions start at 1. A start of 0 doesn’t raise an error, but the result is one character shorter than you asked for:

sqlite> SELECT substr('SQLite', 0, 3) AS start0, substr('SQLite', 1, 3) AS start1;
start0  start1
------  ------
SQ      SQL

Characters, not bytes

“If X is a string then indices count UTF code points. If X is a BLOB then the indices count bytes” (substr()). é is one character but two bytes in UTF-8:

sqlite> SELECT length('café') AS chars, length(CAST('café' AS BLOB)) AS bytes, substr('café', 4, 1) AS char4, hex(substr(CAST('café' AS BLOB), 4, 2)) AS bytes4_5;
chars  bytes  char4  bytes4_5
-----  -----  -----  --------
4      5      é      C3A9

A missing delimiter gives an empty string, not NULL

If the delimiter is missing, instr() returns 0 and the key comes back as an empty string, not NULL:

sqlite> SELECT instr('no-equals-sign', '=') AS pos, quote(substr('no-equals-sign', 1, instr('no-equals-sign', '=') - 1)) AS key, typeof(substr('no-equals-sign', 1, instr('no-equals-sign', '=') - 1)) AS key_type;
pos  key  key_type
---  ---  --------
0    ''   text

That’s why the file-extension example above checks instr() before cutting.

Conclusion

Use substr(text, start, length) with positions that start at 1. A negative start counts from the right, and instr() finds the delimiter when you need to split. Check for a missing delimiter before cutting, and remember that substr() counts characters in text but bytes in a BLOB.