How to Select Everything Before/After a Certain Character in MySQL – SUBSTRING_INDEX()

Posted on by Ian

You can use the MySQL SUBSTRING_INDEX() function to return everything before or after a certain character (or characters) in a string.

This function allows you to specify the delimiter to use, and you can specify which one (in the event that there’s more than one in the string).

Syntax

Here’s the syntax:

SUBSTRING_INDEX(str,delim,count)

Where str is the string, delim is the delimiter (from which you want a substring to the left or right of), and count specifies which delimiter (in the event there are multiple occurrences of the delimiter in the string).

Note that the delimiter can be a single character or multiple characters.

Example 1 – Select Everything to the Left

To select everything before a certain character, use a positive value:

SELECT SUBSTRING_INDEX('Cats,Dogs,Rabbits', ',', 2);

Result:

Cats,Dogs

In this example, we select everything before the second comma. This is done by using a comma (,) as the delimiter, and 2 as the count.

Example 2 – Select Everything to the Right

To select everything after a certain character, you need to use a negative value:

SELECT SUBSTRING_INDEX('Cats,Dogs,Rabbits', ',', -2);

Result:

Dogs,Rabbits

Note that the negative value means that it will count in from the right, then select the substring to the right of the delimiter.

What if the Delimiter doesn’t Match?

If the delimiter isn’t found in the string, the string is simply returned in full.

Example:

SELECT SUBSTRING_INDEX('Cats,Dogs,Rabbits', '-', 1);

Result:

Cats,Dogs,Rabbits

In this case, the whole string is returned in full because we used a hyphen (-) as the delimiter but there were no hyphens in the string.

We’d get the same result if the string does contain the delimiter, but our count exceeds the number of delimiters in the string.

Example:

SELECT SUBSTRING_INDEX('Cats,Dogs,Rabbits', ',', 10);

Result:

Cats,Dogs,Rabbits

Case-Sensitivity

The SUBSTRING_INDEX() function performs a case-sensitive search for the delimiter. That is, the delimiter needs to be the correct case before it will match.

Incorrect Case

Here’s an example where the case doesn’t match:

SELECT SUBSTRING_INDEX('Cats and Dogs and Rabbits', 'AND', 2);

Result:

Cats and Dogs and Rabbits

We get the whole string in full.

Correct Case

Now here’s the same example, but where the case matches:

SELECT SUBSTRING_INDEX('Cats and Dogs and Rabbits', 'and', 2);

Result:

Cats and Dogs