How to split string value in MySQL query

Posted on Oct 01, 2021

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


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  |
+------------+-------------+-----------+

Now you’ve learned how to use the SUBSTRING_INDEX function to split a string.

Let’s look at how to use a stored procedure to split a string by a specific delimiter next.

MySQL split string with a stored procedure

You can also split a string in MySQL by using a stored procedure.

You need to create the following procedure in your database:

DELIMITER //

CREATE PROCEDURE splitString(
  IN inputString text,
  IN delimiterChar CHAR(1)
)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS temp_string;
  CREATE TEMPORARY TABLE temp_string(vals text); 
  WHILE LOCATE(delimiterChar,inputString) > 1 DO
    INSERT INTO temp_string SELECT SUBSTRING_INDEX(inputString,delimiterChar,1);
    SET inputString = REPLACE(inputString, (SELECT LEFT(inputString, LOCATE(delimiterChar, inputString))),'');
  END WHILE;
  INSERT INTO temp_string(vals) VALUES(inputString);
  SELECT TRIM(vals) FROM temp_string;
END; //

DELIMITER ;

The splitString() procedure above accepts two arguments:

  • The inputString for the string to split
  • The delimiterChar for the string delimiter

First, the procedure will create a temporary table to store each part of the string until the delimiter position.

Then, a WHILE loop will be executed as long as the delimiterChar is found in the inputString.

The loop will add the pieces of the string into the temp_string table.

Once all string pieces are added to the table, the SELECT query will retrieve the rows for the string.

Here are some results of running the stored procedure:

CALL splitString('1 > 2 > 3', '>');

-- Output:
+------------+
| TRIM(vals) |
+------------+
| 1          |
| 2          |
| 3          |
+------------+

CALL splitString('hello # good morning # happy to see you', '#');

-- Output:
+------------------+
| TRIM(vals)       |
+------------------+
| hello            |
| good morning     |
| happy to see you |
+------------------+

And that’s how you can split a string using a MySQL stored procedure.

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.

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.