MySQL SUBSTRING: extract part of a string

Quick answer

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

Positions start at 1, not 0. SUBSTR and MID are aliases for the same function.

Syntax

Two forms:

SUBSTRING(str, pos)           -- from pos to the end
SUBSTRING(str, pos, len)      -- len characters from pos

There is also a standard-SQL form that does the same thing:

SUBSTRING(str FROM pos FOR len)

Both work. The comma form is more common in MySQL code; the FROM ... FOR form ports to PostgreSQL.

Positions start at 1

Coming from almost any programming language, this is the thing to internalize:

SELECT SUBSTRING('Hello World', 1, 5) AS from_1,
       SUBSTRING('Hello World', 0, 5) AS from_0;

from_1   from_0
Hello

from_0 is empty. Not an error, not the first five characters. An empty string.

There is no character at position 0, so MySQL returns nothing, quietly. If your substring results are mysteriously blank, an off-by-one from 0-indexed thinking is the first thing to check. This is the single most common SUBSTRING bug.

Negative positions count from the end

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

last_five
World

-5 means "five characters from the end". This is genuinely useful and has no equivalent in LEFT/RIGHT when combined with a length:

SELECT SUBSTRING('2026-08-20', -5, 2) AS month;

month
08

Two characters, starting five from the end.

SUBSTR and MID

Identical functions with different names:

SELECT SUBSTRING('Hello World', 1, 5) AS a,
       SUBSTR('Hello World', 1, 5)    AS b,
       MID('Hello World', 1, 5)       AS c;

a       b       c
Hello   Hello   Hello

SUBSTRING is the standard name and the one to use. SUBSTR is shorter and also exists in PostgreSQL, Oracle, and SQLite. MID is a MySQL-ism inherited from spreadsheet conventions, and there is no reason to prefer it.

LEFT and RIGHT

For a prefix or suffix, these are clearer than SUBSTRING:

SELECT LEFT('Hello World', 5)  AS l,   -- 'Hello'
       RIGHT('Hello World', 5) AS r;   -- 'World'

LEFT(str, n) is SUBSTRING(str, 1, n). RIGHT(str, n) is SUBSTRING(str, -n). Use them when that is what you mean.

SUBSTRING_INDEX: splitting on a delimiter

This is the function people actually want when they reach for SUBSTRING plus LOCATE:

SUBSTRING_INDEX(str, delimiter, count)

A positive count takes from the left, a negative count from the right:

SELECT SUBSTRING_INDEX('ada@example.com', '@', 1)  AS local_part,
       SUBSTRING_INDEX('ada@example.com', '@', -1) AS domain;

local_part   domain
ada          example.com

That is the whole email-splitting problem solved without arithmetic.

For a middle field, nest them:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a.b.c.d', '.', 3), '.', -1) AS third;

third
c

Read it inside out: take the first three fields (a.b.c), then the last field of that (c).

An important edge case: if the delimiter is not present, SUBSTRING_INDEX returns the whole string, not NULL and not an empty string.

SELECT SUBSTRING_INDEX('no-at-sign-here', '@', -1);

no-at-sign-here

So splitting a malformed email gives you the malformed email back as the "domain". Validate before you split, or check afterwards.

Finding a position with LOCATE

When the position is not fixed, find it first:

SELECT LOCATE('@', 'ada@example.com') AS at_position;

at_position
4

LOCATE returns 0 when the substring is absent, which combines badly with SUBSTRING's position-0 behaviour:

SELECT SUBSTRING('no-at-sign', LOCATE('@', 'no-at-sign') + 1) AS domain;

That gives LOCATE = 0, so SUBSTRING(str, 1), so the entire string. A missing delimiter produces a plausible-looking wrong answer rather than an error. SUBSTRING_INDEX is safer for this, or guard it:

SELECT IF(LOCATE('@', email) = 0, NULL, SUBSTRING(email, LOCATE('@', email) + 1))
FROM users;

INSTR(str, substr) is the same as LOCATE with the arguments the other way round, which is a trap in itself. POSITION(substr IN str) is the standard-SQL spelling.

Characters, not bytes

SUBSTRING counts characters, and multi-byte characters count as one:

SELECT SUBSTRING('café', 4, 1) AS c, LENGTH('café') AS bytes, CHAR_LENGTH('café') AS chars;

c    bytes   chars
é    5       4

LENGTH returns bytes (5, because é is two bytes in UTF-8). CHAR_LENGTH returns characters (4). SUBSTRING works in characters, so it lines up with CHAR_LENGTH, not LENGTH.

Mixing them up produces truncation that only appears for non-ASCII data, which typically means it appears in production and not in your tests. Use CHAR_LENGTH unless you specifically want bytes.

SUBSTRING on a BLOB works in bytes, because a BLOB has no character set.

If CHAR_LENGTH disagrees, check your client charset

If CHAR_LENGTH('café') returns 5 rather than 4, MySQL is not wrong. Your connection is not speaking UTF-8, so the server received two separate latin1 characters rather than one multi-byte one.

This is easy to hit by accident. The official mysql:8 Docker image's command-line client defaults to latin1:

SHOW VARIABLES LIKE 'character_set_client';

character_set_client   latin1

Fix the connection, not the query:

mysql --default-character-set=utf8mb4 -u root -p

Or SET NAMES utf8mb4; at the start of a session.

HEX() settles the argument when you are unsure what is actually stored:

SELECT HEX(v), LENGTH(v), CHAR_LENGTH(v) FROM mb;

636166C3A9   5   4

63 61 66 is caf, and C3A9 is é as two bytes. Five bytes, four characters. If the hex looks wrong, the data was mangled on the way in and no amount of SUBSTRING will fix it.

NULL handling

Any NULL argument gives NULL:

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

Both NULL. Wrap with COALESCE if you need a default:

SELECT SUBSTRING(COALESCE(name, ''), 1, 5) FROM users;

Indexes and performance

Wrapping a column in SUBSTRING prevents MySQL using an index on it:

-- Cannot use an index on email
SELECT * FROM users WHERE SUBSTRING(email, -11) = 'example.com';

For prefix matching, LIKE with a trailing wildcard can use an index:

SELECT * FROM users WHERE email LIKE 'ada%';       -- can use an index
SELECT * FROM users WHERE email LIKE '%example';   -- cannot, leading wildcard

For suffix matching, the usual trick is to store the reversed value in a generated column and index that:

ALTER TABLE users
  ADD COLUMN email_domain VARCHAR(255)
  GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED,
  ADD INDEX (email_domain);

Now WHERE email_domain = 'example.com' is an index lookup. Generated columns need MySQL 5.7 or later, and STORED (rather than VIRTUAL) to be indexable in most cases.

Common problems

My substring is empty. You passed position 0. Positions start at 1.

My substring is off by one. Same cause, from a different direction. SUBSTRING(s, 1, 5) gives five characters starting at the first.

Splitting on a delimiter returns the whole string. The delimiter is not in the string. SUBSTRING_INDEX returns the input unchanged, and LOCATE returns 0.

Multi-byte characters are truncated. You used LENGTH where you needed CHAR_LENGTH.

The query got slow. SUBSTRING in WHERE disables the index. Use a generated column, or LIKE 'prefix%'.

Quick reference