If you are looking for the solution to the question “How To MySQL Split String By Delimiter?”, let’s read our article. In this tutorial, we will show you some ways to separate a sequence of strings by delimiter .
How To MySQL Split String By Delimiter
When working with Struct Query Language, it is much harder to work with data types than programming languages. In MySQL, there is only one function that supports splitting a string by delimiter, and it is the function SUBSTRING_INDEX().
Syntax:
SELECT SUBSTRING_INDEX("string", "delimiter", number)
The function receives three parameters: string, delimiter, and number:
- string: The string that is needed to split.
- delimiter: The type of delimiter that use to determine how to separate the string.
- number: The number of substrings from the original string.
Before moving on to solutions, we will create a table that contains strings that will be used to split in the next part.
Code:
create table World_Cup_2018(
country varchar(255),
player varchar(255)
);
insert into World_Cup_2018 (country, player) values
('Spain', 'David de Gea, Isco, Diego Costa'),
('Germany', 'Mesut Özil, Thomas Müller, Manuel Neuer'),
('England', 'Harry Maguire, Harry Kane, Raheem Sterling');
Select * from World_Cup_2018
Result:
country player
Spain David de Gea, Isco, Diego Costa
Germany Mesut Özil, Thomas Müller, Manuel Neuer
England Harry Maguire, Harry Kane, Raheem Sterling
Now, let’s move on and see how we can use this function to split the sequence of strings by delimiter.
A few ways to use the function
Split the string into two parts
First, we will discover the lowest level to split the string. In this approach, we only call a single function for each query. We pass the player column to the string parameter to split this field, pass the comma’s delimiter and specify the number of substrings to the number parameter.
Code:
# Query to split the string in player column
# Select country column
select
country,
# Select the first player and display as First_Play column
substring_index(player, ',', 1) as First_Player,
# Select the last players rest and display as Rest_Play column
substring_index(player, ',', -2) as Rest_Player
from
World_Cup_2018
Result:
country First_Player Rest_Player
Spain David de Gea Isco, Diego Costa
Germany Mesut Özil Thomas Müller, Manuel Neuer
England Harry Maguire Harry Kane, Raheem Sterling
Split the string into more than two parts
When splitting into more than two substrings, we must combine a few functions into a command. In the below example, we use double functions; the parent function is used to split and take 2 substrings. Then the child function is used to take one of them.
Code:
# Query to split the string in player column
# Select country column
select
country,
# Select the first player from (select 2 first player)
substring_index(
substring_index(player, ',', 2),
',',
1
) as First_Player,
# Select the second player from (select 2 first player)
substring_index(
substring_index(player, ',', 2),
',',
-1
) as Second_Player,
# Select the last player
substring_index(player, ',', -1) as Rest_Player
from
World_Cup_2018
Result:
country First_Player Second_Player Rest_Player
Spain David de Gea Isco Diego Costa
Germany Mesut Özil Thomas Müller Manuel Neuer
England Harry Maguire Harry Kane Raheem Sterling
Summary
Congratulations! You have finished our article to get the answer to the question “How To MySQL Split String By Delimiter?”. Now you have enough knowledge to split strings in MySQL by yourself. If you still have any confusion, leave your questions as comments.
Maybe you are interested:
- Error 1273 (Hy000) At Line 25 Unknown Collation: ‘Utf8mb4_0900_Ai_Ci’
- MySQL Command Not Found
- Fix MySQL ERROR 1452 a foreign key constraint fails

My name is Robert Collier. I graduated in IT at HUST university. My interest is learning programming languages; my strengths are Python, C, C++, and Machine Learning/Deep Learning/NLP. I will share all the knowledge I have through my articles. Hope you like them.
Name of the university: HUST
Major: IT
Programming Languages: Python, C, C++, Machine Learning/Deep Learning/NLP