SQLite SUBSTR: extract part of a string

Quick answer

SELECT substr('Hello World', 1, 5);   -- 'Hello'
SELECT substr('Hello World', 7);      -- 'World'
SELECT substr('Hello World', -5);     -- 'World'

Positions start at 1. substring() is an alias for substr() and works identically.

Syntax

substr(X, Y)      -- from position Y to the end
substr(X, Y, Z)   -- Z characters starting at Y

substring(X, Y, Z) is accepted as an alias. substr is the traditional SQLite spelling and the one you will see in most code.

Positions start at 1

SELECT substr('Hello World', 1, 5) AS from_1,
       substr('Hello World', 7)    AS to_end;

from_1|to_end
Hello|World

The first character is at position 1, not 0.

Position 0 does not do what MySQL does

This is the one worth knowing, and it is a real portability trap:

SELECT '[' || substr('Hello World', 0, 5) || ']' AS from_0;

[Hell]

SQLite returns 'Hell'. MySQL returns an empty string for the same call.

The reason is that SQLite treats position 0 as a real (if imaginary) position one before the first character, then counts 5 characters forward from there: positions 0, 1, 2, 3, 4. Position 0 holds nothing, so you get the four characters at 1 through 4.

MySQL instead decides there is no position 0 and returns nothing at all.

So substr(x, 0, n) silently returns n-1 characters in SQLite and zero characters in MySQL. Neither errors. If you are porting queries between the two, or writing something that runs on both, this produces a wrong answer that looks plausible in one engine and empty in the other.

Use position 1. There is no case where 0 is what you meant.

Negative positions count from the end

SELECT substr('Hello World', -5) AS last_five;

World

-5 means "start five characters from the end". With a length:

SELECT substr('2026-09-07', -5, 2) AS month;

09

This is the cleanest way to take a suffix, since SQLite has no RIGHT() function.

SQLite has no LEFT or RIGHT

Unlike MySQL, there is no LEFT() or RIGHT(). Use substr:

SELECT substr('Hello World', 1, 5)  AS left_5;    -- LEFT(x, 5)
SELECT substr('Hello World', -5)    AS right_5;   -- RIGHT(x, 5)

Queries ported from MySQL will fail with:

Error: no such function: LEFT

SQLite has no SUBSTRING_INDEX

MySQL's delimiter-splitting function does not exist here:

SELECT substring_index('ada@example.com', '@', 1);

Error: in prepare, no such function: substring_index

Build it from instr and substr instead.

Everything before a delimiter:

SELECT substr('ada@example.com', 1, instr('ada@example.com', '@') - 1);

ada

Everything after a delimiter:

SELECT substr('ada@example.com', instr('ada@example.com', '@') + 1);

example.com

The instr-returns-0 trap

instr returns 0 when the substring is absent:

SELECT instr('ada@example.com', '@') AS found, instr('no-at', '@') AS absent;

found|absent
4|0

So when the delimiter is missing, the arithmetic quietly produces a negative length:

-- '@' is absent, so instr returns 0, and this becomes substr(x, 1, -1)
SELECT '[' || substr('no-at-sign', 1, instr('no-at-sign', '@') - 1) || ']';

[]

An empty string, with no error. So a malformed email produces an empty "local part" rather than a failure, and a row that should have been rejected flows onward looking merely blank. Guard it:

SELECT CASE
         WHEN instr(email, '@') = 0 THEN NULL
         ELSE substr(email, instr(email, '@') + 1)
       END AS domain
FROM users;

If you are doing this a lot, a generated column plus an index turns the extraction into a lookup:

ALTER TABLE users ADD COLUMN domain TEXT
  GENERATED ALWAYS AS (
    CASE WHEN instr(email, '@') = 0 THEN NULL
         ELSE substr(email, instr(email, '@') + 1) END
  ) VIRTUAL;

CREATE INDEX users_domain ON users (domain);

Note VIRTUAL, not STORED. SQLite will not let you add a stored generated column to an existing table:

Runtime error: cannot add a STORED column

STORED is only available when you declare the column in the original CREATE TABLE. VIRTUAL computes the value on read, and unlike MySQL, SQLite lets you index a virtual generated column, so the index gives you the lookup speed without the stored bytes.

Generated columns need SQLite 3.31 or later.

Characters, not bytes

substr counts characters when the value is text:

SELECT length('café')                AS chars,
       length(CAST('café' AS BLOB))  AS blob_bytes,
       substr('café', 4, 1)          AS fourth;

chars|blob_bytes|fourth
4|5|é

length() on text returns characters (4). length() on a blob returns bytes (5, because é is two bytes in UTF-8). substr follows the same rule: characters for text, bytes for blobs.

This is more forgiving than MySQL, where LENGTH() always returns bytes and you need CHAR_LENGTH() for characters. But it means length() changes meaning based on the value's type, which SQLite decides per value rather than per column. A column declared TEXT can still hold a blob.

Use typeof(col) when a length looks wrong.

NULL handling

Any NULL argument gives NULL:

SELECT substr(NULL, 1, 5), substr('Hello', NULL, 5);

Both NULL. Use coalesce for a default:

SELECT substr(coalesce(name, ''), 1, 5) FROM users;

Common problems

My substring is one character short. You passed position 0 and got n-1 characters. Use 1.

The same query returns different results in MySQL and SQLite. Almost certainly substr(x, 0, n). See above.

no such function: LEFT / RIGHT / SUBSTRING_INDEX. MySQL functions that SQLite does not have. Use substr with a negative position, or instr plus substr.

Splitting on a delimiter returns garbage. The delimiter is absent, so instr returned 0. Guard with CASE.

A length looks wrong for non-ASCII text. Check typeof(). If the value is a blob, length is counting bytes.

Quick reference