In MariaDB, you can use the SUBSTRING_INDEX() function to return everything before or after a certain character (or characters) in a string.

This function accepts three arguments; the string, the delimiter, and the number of occurrences of that delimiter to use for determining the substring to return.

Examples

We can see how the function works in the following examples.

Select Everything Before

To select everything before a certain character, make the third argument a positive value:

SELECT SUBSTRING_INDEX('Red,Green,Blue', ',', 2);

Result:

Red,Green

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.

I used a comma in that example, but it could’ve been any character or string:

SELECT SUBSTRING_INDEX('Red or Green or Blue', ' or ', 2);

Result:

Red or Green

Select Everything After

Use a negative value to select everything after a certain character:

SELECT SUBSTRING_INDEX('Red,Green,Blue', ',', -2);

Result:

Green,Blue

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

When the Delimiter isn’t Found

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

Example:

SELECT SUBSTRING_INDEX('Red,Green,Blue', '-', 1);

Result:

Red,Green,Blue

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('Red,Green,Blue', ',', 10);

Result:

Red,Green,Blue

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('Red and green and blue', 'AND', 2);

Result:

Red and green and blue

We get the whole string in full.

Correct Case

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

SELECT SUBSTRING_INDEX('Red and green and blue', 'and', 2);

Result:

Red and green