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 function syntax 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 = REGEXP_REPLACE(inputString, (
SELECT LEFT(inputString, LOCATE(delimiterChar, inputString))
),'',1,1);
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
inputStringfor the string to split - The
delimiterCharfor 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 parts of the string into the temp_string table, then call the REGEXP_REPLACE() function to remove the part that’s already added to the table.
Once all string parts 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.
I hope this tutorial is helpful. Until next time! 🙏
