How to split string value in MySQL query

Learn how to split string values in MySQL with the help of the SUBSTRING_INDEX function

Posted on October 01, 2021


To split a string in MySQL, you need to make use of the SUBSTRING_INDEX function that is provided by MySQL.

The SUBSTRING_INDEX() function allows you to extract a part of a complete string.

The syntax of the function is as follows:

SUBSTRING_INDEX(expression, delimiter, count);

The function requires you to pass 3 parameters as described below:

  • The first parameter will be the string source to operate on. You can pass an expression or a column name
  • The second parameter will be the delimiter that tells the function where to stop the extraction
  • The third parameter will be the occurrence count of the delimiter. You can pass a positive or negative number

Let’s see an example of the SUBSTRING_INDEX() in action.

Suppose you have an address string as follows:

9058 Goldfield Avenue\n Yonkers, NY 10701

Let’s say you want to split the address above based on the appearance of the \n newline symbol.

Here’s how you do it with the SUBSTRING_INDEX() function:

SELECT 
  SUBSTRING_INDEX("9058 Goldfield Avenue\n Yonkers, NY 10701", '\n', 1) 
    AS address_one,
  SUBSTRING_INDEX("9058 Goldfield Avenue\n Yonkers, NY 10701", '\n', -1) 
    AS address_two;

The query above will return the following result set:

+-----------------------+--------------------+
| address_one           | address_two        |
+-----------------------+--------------------+
| 9058 Goldfield Avenue |  Yonkers, NY 10701 |
+-----------------------+--------------------+

The SUBSTRING_INDEX() function scans the string source for the delimiter string, then extracts the string based on the occurrence count of the delimiter that you passed as the third parameter.

When you pass a negative number, then the function will traverse the string from the end instead of from the beginning of the string.

Let’s see another example with MySQL table data. Suppose you have a students table with the following data:

+----+---------------+
| id | name          |
+----+---------------+
|  1 | Mark Crane    |
|  2 | Natalia Smith |
|  3 | Gary Anderson |
+----+---------------+

You can split the strings in the name column above as first_name and last_name by using the SUBSTRING_INDEX function as follows:

SELECT
  SUBSTRING_INDEX(name,' ', 1) AS first_name, 
  SUBSTRING_INDEX(name,' ', -1) AS last_name 
  FROM students;

The SQL query above will return the following result set:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Mark       | Crane     |
| Natalia    | Smith     |
| Gary       | Anderson  |
+------------+-----------+

The SUBSTRING_INDEX() function can be called as the first parameter of another SUBSTRING_INDEX() call so that you can extract a part of the string that’s in the middle of the delimiters.

Suppose your students have middle names as follows:

+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | Mark Argus Crane     |
|  2 | Natalia Steven Smith |
|  3 | Gary Poul Anderson   |
+----+----------------------+

At first, it seems that calling SUBSTRING_INDEX() and passing the number 2 as the third parameter might work.

But passing the number 2 will cause the function to extract both the first name and the middle name as shown below:

mysql> SELECT SUBSTRING_INDEX(name,' ', 2) as middle_name FROM students; 
+----------------+
| middle_name    |
+----------------+
| Mark Argus     |
| Natalia Steven |
| Gary Poul      |
+----------------+

To extract only the middle name, you need to call the SUBSTRING_INDEX function twice, with the second call as the first parameter of the function.

The inner function call should be the same as above, while the outer call should extract the string using -1 as the third parameter:

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) AS middle_name
  FROM students;

Now the middle name should be extracted correctly as shown below:

+-------------+
| middle_name |
+-------------+
| Argus       |
| Steven      |
| Poul        |
+-------------+

The complete SQL query below:

SELECT
  SUBSTRING_INDEX(name,' ', 1) AS first_name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2),' ', -1) AS middle_name, 
  SUBSTRING_INDEX(name,' ', -1) AS last_name
  FROM students;

Will produce the following result set:

+------------+-------------+-----------+
| first_name | middle_name | last_name |
+------------+-------------+-----------+
| Mark       | Argus       | Crane     |
| Natalia    | Steven      | Smith     |
| Gary       | Poul        | Anderson  |
+------------+-------------+-----------+

And that’s how you can split a string in MySQL.

If you’re developing a web application project, it’s better to let the programming language that handles the backend of your application to split the string instead of using MySQL queries.

For example, JavaScript has the split() method that allows you to split a string into an array:

JavaScript code recipe: split string into array

Languages like JavaScript and PHP have many functions that can help you manipulate string values with more precision.

But if you must do it from MySQL, then the SUBSTRING_INDEX() function will be your best friend.

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.