How to use SUBSTRING() in MySQL

In MySQL, the SUBSTRING() function enables you to return a substring from a string. So you can use this function to grab a portion of text from a larger piece of text.

There are several different ways to use the SUBSTRING() function, and this affects the syntax.

Example 1 – Basic Usage

The most basic usage goes like this:

SUBSTRING(str,pos)

In this case, str is the string, and pos is the position to start the substring from.

Here’s an example of this basic syntax:

SELECT SUBSTRING('Cats and dogs', 6);

Result:

and dogs

In this example, we take a substring from the string Cats and dogs, starting at position 6.

Example 2 – Using the FROM Clause

Another way to do it:

SUBSTRING(str FROM pos)

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

Here’s the same example as the previous one, except here we’ve changed it to use the FROM syntax:

SELECT SUBSTRING('Cats and dogs' FROM 6);

Result:

and dogs

So we get the same result.

Example 3 – Add a Length for the Substring

You can also add a length:

SUBSTRING(str,pos,len)

Here, len is the length of the substring to return. This enables us to return a substring with a specified length.

Here’s an example of doing that:

SELECT SUBSTRING('Cats and dogs', 6, 3);

Result:

and

So in this case we choose to return only 3 characters from the starting position.

Example 4 – Using FOR

Finally, when using FROM, you can also add the FOR to provide the length.

SUBSTRING(str FROM pos FOR len)

And here’s the same example as previous, except here we use the FROM clause as well as FOR:

SELECT SUBSTRING('Cats and dogs' FROM 6 FOR 3);

Result:

and

So as with the previous example, this selects 3 characters from the starting position.

Example 5  – Counting Backwards

You can also use a negative value for the starting position. In this case, the position is taken counting backwards from the end of the string:

SELECT SUBSTRING('Cats and dogs', -6);

Result:

d dogs

Example 6 – The SUBSTR() Function

You can also use SUBSTR() which is a synonym for SUBSTRING(). So any of the previous examples could be rewritten using SUBSTR() instead.

Example:

SELECT SUBSTR('Cats and dogs', 6);

Result:

and dogs

Example 7 – The MID() Function

And there’s yet another synonym for SUBSTRING() – the MID() function. So any of the previous examples could be rewritten using MID() instead.

Example:

SELECT MID('Cats and dogs', 6);

Result:

and dogs