How to Split String Into Rows in MySQL

1. Split String Into Rows in MySQL
2. Split String Into Rows Using the SUBSTRING_INDEX() Method
3. Conclusion

MySQL stores data in table columns and rows, and users can define, control, manipulate and query those data. MySQL gives us various features, and one feature we can have is split strings into rows.

This article discusses splitting a string into rows, its uses, and how to perform it in MySQL.

Split String Into Rows in MySQL

When we manipulate the data in databases, sometimes we interact with strings. For example, a table can include customers’ full names, their addresses, and some descriptions.

Sometimes, we add those data as strings, and we might need to break them into rows for various purposes. To accomplish this goal, MySQL provides us with a method, SUBSTRING_INDEX().

Split String Into Rows Using the SUBSTRING_INDEX() Method

SUBSTRING_INDEX() is a feature that MySQL provides us to derive a substring from a string. It checks for a specific delimiter, so the substring before it will be outputted.

The syntax is as below:

SUBSTRING_INDEX(string, delimiter, number);

In the above syntax, there are three parameters. The string refers to the row we input to derive substrings, and the delimiter is the value that the function will search for.

The number of times the delimiter will be searched is called the number. The number can be either a positive number or a negative number.

If it is a positive number, we will get the substring left to the delimiter from the front to the back of the string. If it is a negative number, we will get the substring right to the delimiter and start searching the delimiter from the back to the front of the string.

Let’s try an example.

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',2) AS newp;

As shown above, we have given a string containing four countries separated by commas. Then as the delimiter, the comma has passed, and as the number, we assigned two.

Let’s see the result.

Output:

As you can see, we get the substring left to the second comma since we have given two as the number. Let’s try this again with a negative number as for number.

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',-1) AS newp;

Below is the result of the above code, and as you can see, we get China as the output because we have given -1 as the number, and since it’s a negative number, it searched the last delimiter and outputted the substring that is right to it.

Output:

Now, let’s see how we can split a string into rows using this method. Refer to the below example.

DELIMITER //

-- Creating a procedure
CREATE procedure newProcedure()

BEGIN
-- Declaring a variable as myvar to store the string
DECLARE myvar varchar (300);

-- Assigning the string to the variable
SET myvar = 'China.Japan.USA';

-- Deriving the substrings
(SELECT SUBSTRING_INDEX(myvar, '.', 1) AS Countries)
UNION
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(myvar, '.', 2),'.','-1') AS Countries)
UNION
(SELECT SUBSTRING_INDEX(myvar, '.', -1) AS Countries);
END //
DELIMITER ;

-- Calling the procedure
CALL newProcedure()

In the above code, we have created a procedure called the newProcedure and then declared a variable to store the string. The string contains full-stop symbols so that it will be our delimiter.

We have used the SUBSTING_INDEX method four times (two times in the middle to get the middle substring) and UNION to have those substrings in a single column. At last, we have called our procedure, and the output is below.

Output:

We have split the string into rows, but this code will be challenging when we have more than three full-stop symbols.

To overcome this issue, we can build a solution. First, we can create a table with a single column that includes country names.

The country names we add have more than one name since we need to split the full name. Let’s create a table that has three country names as follows.

CREATE TABLE countries (countryName VARCHAR(100));
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

Now let’s create another table called the numberList, which includes one, two, and three as the data.

CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

The purpose of this table is to set a maximum number of substrings to split. We have added three digits, and when the code executes, it breaks the string into a maximum of three substrings, even if it has four substrings inside the line.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

In the above code chunk, we have used the SUBSTRING_INDEX function, and inside it, we have put it as the delimiter to search since the country names are separated with spaces. Then we checked whether the country names have more than three substrings.

After that, we printed the substrings in rows.

Full Code:

-- Creating the table
CREATE TABLE countries (countryName VARCHAR(100));

-- Inserting values
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

-- Create a table that contains the numbers
CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

-- Deriving the substrings
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

Below is the result we get after running the code.

Output:

As you can see, the country names are divided into substrings, as we expected.

Conclusion

Through this article, we had a brief introduction to MySQL. Then we learned why we need to split a string into rows and how to perform it in MySQL.

To achieve the goal, MySQL provided us with the SUBSTRING_INDEX method, and we looked at how to use it to complete the task.

There are other methods to do this task, such as the STRING_SPLIT function, but the technique we discussed in this article is the best way in MySQL.