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:
- 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.
- 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:
- 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
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 split and extract from string
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:
, '_', 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.
, '_', -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.
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;
Java, Scala, Groovy Python, R, GoLang