How to Use SUBSTRING in MySQL: Syntax and Examples

In MySQL, use the SUBSTRING() function to extract a part of a string by specifying the starting position and optional length. The syntax is SUBSTRING(string, start, length), where start is the position to begin extraction and length is how many characters to take.

Syntax

The SUBSTRING() function extracts a substring from a given string.

• string: The original text you want to cut from.
• start: The position to start extracting (1-based index).
• length (optional): How many characters to extract from the start position.

If length is omitted, it extracts from start to the end of the string.

sql

SUBSTRING(string, start, length)

-- or --

SUBSTRING(string, start)

Example

This example shows how to extract parts of a string using SUBSTRING(). It extracts 'ell' from 'Hello' starting at position 2 for 3 characters, and extracts 'World' from 'Hello World' starting at position 7 to the end.

sql

SELECT SUBSTRING('Hello', 2, 3) AS part1, SUBSTRING('Hello World', 7) AS part2;

Output

part1 | part2
------|-------
ell   | World

Common Pitfalls

Common mistakes when using SUBSTRING() include:

• Using a start position less than 1, which returns an empty string.
• Forgetting that string positions start at 1, not 0.
• Not specifying length when you want a specific number of characters, which extracts till the end.

sql

/* Wrong: start position 0 returns empty string */
SELECT SUBSTRING('Hello', 0, 2) AS wrong;

/* Correct: start position 1 returns first two characters */
SELECT SUBSTRING('Hello', 1, 2) AS correct;

Output

wrong | correct
------|--------
      | He

Quick Reference

Usage	Description
SUBSTRING(string, start)	Extract substring from start position to end
SUBSTRING(string, start, length)	Extract substring from start position with given length
start < 1	Returns empty string
start = 1	Starts from first character

Key Takeaways

Use SUBSTRING(string, start, length) to extract part of a string in MySQL.

Start position counts from 1, not 0.

If length is omitted, extraction continues to the string's end.

Start positions less than 1 return an empty string.

Always specify length if you want a fixed number of characters.