SUBSTRING and LEFT/RIGHT in MySQL

Introduction

These functions help you get parts of text from a bigger text. It's like cutting a piece from a long word or sentence.

You want to get the first few letters of a name to create a nickname.

You need to extract the area code from a phone number.

You want to get the last few characters of a product code to check its type.

You want to get a middle part of a sentence to analyze it.

You want to shorten long text to show only a preview.

Syntax

MySQL

SUBSTRING(text, start_position, length)
LEFT(text, number_of_characters)
RIGHT(text, number_of_characters)

Positions start at 1, not 0.

If length is omitted in SUBSTRING, it returns from start_position to the end.

Examples

Gets first 5 characters: 'Hello'

MySQL

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

Gets first 5 characters from left: 'Hello'

MySQL

SELECT LEFT('Hello World', 5);

Gets last 5 characters from right: 'World'

MySQL

SELECT RIGHT('Hello World', 5);

Gets substring from position 7 to end: 'World'

MySQL

SELECT SUBSTRING('Hello World', 7);

Sample Program

This query shows how to get parts of the text 'Database Learning' using SUBSTRING, LEFT, and RIGHT.

MySQL

SELECT
  SUBSTRING('Database Learning', 1, 8) AS substring_example,
  LEFT('Database Learning', 8) AS left_example,
  RIGHT('Database Learning', 8) AS right_example;

Important Notes

SUBSTRING, LEFT, and RIGHT all count positions starting at 1, not 0 like some programming languages.

If you ask for more characters than the text has, it just returns what is available without error.

Use LEFT when you want characters from the start, RIGHT for the end, and SUBSTRING for any part in the middle.

Summary

SUBSTRING extracts a part of text starting at any position.

LEFT gets characters from the start of the text.

RIGHT gets characters from the end of the text.