SQLite extracts part of a string with substr(X, Y, Z): the text X, starting at character Y, Z characters long. 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()). Positions start at 1.
substr(text, start, length) returns part of a string; leave out length to read to the end, and use a negative start to count from the right. substring() is the same function (SQLite 3.34+). Combine it with instr() to split on a delimiter.
Every example on this page was run on 6 October 2026 with SQLite 3.51.0 in the sqlite3 shell. Lines starting with sqlite> show the statement; the lines after it are its output.
Basic use
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“”substring()” is an alias for “substr()” beginning with SQLite version 3.34” (substr()).
Negative start and length
“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 QLStarting at 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 SQLCharacters, 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 é C3A9Split 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 30If 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, substr('no-equals-sign', 1, instr('no-equals-sign', '=') - 1) AS key;
pos key
--- ---
0Guard against that with CASE, for example to get a file extension:
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
READMEFilter with substr()
sqlite> SELECT path FROM files WHERE substr(path, 1, 5) = 'docs/';
path
-------------
docs/intro.mdLIKE and GLOB can do the same prefix test, with differences: LIKE ignores case for ASCII letters, while substr() and GLOB don’t, and 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 0Related
string functions in the SQLite cheat sheet, string concatenation, LIKE, and the official reference: SQLite core functions.