JIYIK CN >

MySQL stores data in columns and rows of tables, which users can define, control, manipulate, and query. MySQL provides us with various functions, and one function we can have is to split a string into rows.

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

Splitting a string into rows in MySQL

When we manipulate data in a database, sometimes we interact with strings. For example, a table could contain a customer's full name, their address, and some description.

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

Use SUBSTRING_INDEX() method to split a string into rows

SUBSTRING_INDEX()It is a feature provided by MySQL to derive a substring from a string. It checks for a specific delimiter and thus will output the substring preceding it.

The syntax is as follows:

SUBSTRING_INDEX(string, delimiter, number);

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

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

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

Let’s take 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 a separator, a comma has been passed, and as a number, we have assigned two.

Let's see the results.

Output:

As you can see, we got the substring to the left of the second comma because we have given 2 as the number. Let's try it again with a negative number as the number.

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

Below is the result of the above code, as you can see, we got 中国 as the output because we gave -1 as the number, as it is a negative number, it searched for the last delimiter and output the correct substring given to it.

Output:

Now, let us see how to use this method to split a string into lines. Refer to the following 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 code above, we created a procedure called newProcedure and then declared a variable to store a string. The string contains a period, so it will be our delimiter.

We have used the SUBSTING_INDEX method four times (twice in the middle to get the middle substring) and used UNION to put these substrings in one column. Finally, we called our program and the output is as follows.

Output:

We have split the string into multiple lines, but this code will be challenging when we have more than three periods.

To overcome this problem, we can build a solution. First, we can create a single-column table containing the country names.

The country names we added have multiple names because we need to split the full name. Let us create a table containing three country names as shown below.

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

Now let us create another table named numberList containing one, two and three as data.

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

The purpose of this table is to set the maximum number of substrings to split. We added three numbers and when the code executes, it will split the string into a maximum of three substrings, even if there are four substrings within 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 block, we have used the SUBSTRING_INDEX function and in it, we have searched for it as a delimiter since the country names are separated by spaces. Then we have checked if there are more than three substrings in the country name.

After that, we print the substrings line by line.

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

Here is the result we get after running the code.

Output:

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

Summarize

Through this article, we had a brief introduction to MySQL. Then we understood why we need to split a string into lines and how to do it in MySQL.

To achieve this goal, MySQL provides us with SUBSTRING_INDEXa method. Let's see how to use it to complete the task.

There are other ways to accomplish this task, such as STRING_SPLITfunctions, but the techniques we discuss in this article are the best approach in MySQL.