How to Split a String in MySQL

Problem:

You’d like to split a string in MySQL.

Example:

Our database has a table named Student with data in the columns id and name.

Let’s fetch the data from the column name and split it into firstname and lastname.

Solution:

We’ll use the SUBSTRING_INDEX() function. Here’s the query:

SELECT
  SUBSTRING_INDEX(name,' ',1) AS firstname,
  SUBSTRING_INDEX(name,' ',-1) AS lastname
FROM Student

Here’s the result of the query:

Discussion:

The function SUBSTRING_INDEX() takes 3 arguments: the source string, the delimiter, and the occurrence count of the delimiter.

The source string is the string that we would like to split.

The delimiter is a string of characters that the SUBSTRING_INDEX() function looks for in the source string. When found, it indicates the place where the substring ends. The delimiter can be a single character such as a space, but it can also be a pattern that consists of two or more characters. This pattern is case sensitive; that is, it matters whether the given characters are upper or lower case. Remember that the delimiter is a string, so it must be written in quotes ('...').

The last argument is the occurrence count. It indicates the number of times the delimiter pattern needs to be matched. Note that the occurrence count can be a negative value. When it is negative, we count the occurrences of the delimiter from the right. When the count is a positive number, we count from the left.

The function outputs every character of the source string until the delimiter is matched the number of times given by the occurrence count.

If we run the following query:

SELECT SUBSTRING_INDEX('Ann Smith', ' ', 1);

the output will be:

Ann

As you can see, the function returned all the characters until the first space from the left was found.

In our example, we retrieve as the student’s first name all the characters up to the first space from the left and as the student’s last name all the characters up to the first space from the right.