How The SUBSTR() Function Works in MySQL

In MySQL, the SUBSTR() function returns a substring starting from the specified position.

Both SUBSTR() and MID() are synonyms of SUBSTRING().

Syntax

The basic syntax goes like this:

SUBSTR(str,pos,len)

Here, str is the string, pos is the position to start the substring from, and len is an optional argument that determines the number of characters to return from that starting position.

There are several variations on how you can use this function, so the full range of syntaxes looks like this:

SUBSTR(str,pos)
SUBSTR(str FROM pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos FOR len)

These are demonstrated in the following examples.

Example 1 – Basic Usage

Here’s an example of the SUBSTR(str,pos) syntax:

SELECT SUBSTR('I play the drums', 3) Result;

Result:

+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+

In this example, I take a substring from the string, starting at position 3.

Example 2 – Using the FROM Clause

Here’s how to do the same thing, but this time using the SUBSTR(str FROM pos) syntax:

SELECT SUBSTR('I play the drums' FROM 3) Result;

Result:

+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+

So we get the same result.

In this case, FROM is standard SQL. Note that this syntax doesn’t use commas.

Example 3 – Specify a Length

In this example, I use the SUBSTR(str,pos,len) syntax:

SELECT SUBSTR('I play the drums', 3, 4) Result;

Result:

+--------+
| Result |
+--------+
| play   |
+--------+

Here I specify that the returned substring should be 4 characters long.

Example 4 – Specify a Length (using the FOR Clause)

In this example, I use the SUBSTR(str FROM pos FOR len) syntax:

SELECT SUBSTR('I play the drums' FROM 3 FOR 5) Result;

Result:

+--------+
| Result |
+--------+
| play   |
+--------+

So this time we used standard SQL to achieve the same result