How To MySQL Split String By Delimiter

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

Robert Collier

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