MySQL how to split and/or transform a string

John D K -

In this article you can see how to split strings in MySQL or how to transform them by changing one separator by another. There are two examples in this article:

  • 15_10_2018 -> 15/10/2018

    • my_example_word -> my/example/word - this example is the simpler one and treats strings with only one separator in them by replacing all of them by using method replace.

  • 15_10_2018 -> 15, 10, 2018

    • my_example_word -> my, example, word - this one is a bit more complicated and create substrings based on search character. It use several functions like:
    • locate
    • mid
    • substring_index

  • Java Scala -> Java, Scala

    • separating MySQL columns by space

MySQL convert column from my_example_word to my/example/word

This example can be applied over dates and other formats which need to be converted to a different form. Let say that you have dates stored as a string with separator '_' - underscore - then you can use MySQL function replace to achieve a simple transformation:

select `date`,  replace(`date`, '_', '/') 
from my_table 
where items between 1 and 10

result:

15_10_2018, 15/10/2018
16_10_2018, 16/10/2018
17_10_2018, 17/10/2018

You can work with several separators but if you need a more complex formatting you will need to use another technique. Next sections shows different approach of solving similar problem. If you want to see more about dates and MySQL then you can check this:

MySQL How to select day, month and year from a date

MySQL how to split and extract from string

For more complex transformation we are going to use 3 MySQL functions like:
* locate
* mid
* substring_index

Lets have the same information in the table as the previous example: 15_10_2018. And now we want to split this string to 3 different strings by the separator '_' - underscore. Below you can see the example how to achieve it:

select 
`date`,  
replace(`date`, '_', '/'),
SUBSTRING_INDEX(`date`, '_', 1) AS day,
MID(`date`, LOCATE('_', `date`, 1) + 1 , LOCATE('_', `date`, 3) -
LOCATE('_', `date`, 1) - 1) AS month,
SUBSTRING_INDEX(`date`, '_', -1) AS year
from my_table

the result of this query will be:

15_10_2018, 15/10/2018, 15, 10, 2018
16_10_2018, 16/10/2018, 16, 10, 2018
17_10_2018, 17/10/2018, 17, 10, 2018

Some explanation about the example:

  • SUBSTRING_INDEX(date, '_', 1) AS day - will return the substring from the beginning to the first match character. It behaves similarly to excel left function. So in the example above 15_10_2018 -> 15, 10, 2018.
  • SUBSTRING_INDEX(date, '_', -1) AS day - will return the substring from the beginning to the first match character. It behaves similarly to excel right function. So in the example above 15_10_2018 -> 2018
  • this code line:
MID(`date`, LOCATE('_', `date`, 1) + 1 , LOCATE('_', `date`, 3) -
LOCATE('_', `date`, 1) - 1) AS month

* extract substring from a given begin to a start
* first we will identify the first separator index
* then we will find the next one

You can use this approach for more complex situation and split strings by different separators.

MySQL how to split string by spaces

If you want to split columns by spaces then you have similar way to do it. In this examples we can see how to split sentences into separator SQL columns from SQL query.

Lets have:

Java Scala Groovy
Python R GoLang

In order to split the languages we can do:

SELECT 
SUBSTRING_INDEX(lang, ' ', 1) AS first_lang,
SUBSTRING_INDEX(SUBSTRING_INDEX(lang,' ', 2), ' ',-1) AS second_lang,
SUBSTRING_INDEX(lang, ' ', -1) as third_lang 
FROM mytable;

result:

Java, Scala, Groovy
Python, R, GoLang