Split column values on each occurrence of a pattern and put it in multiple rows in MySQL

userWkp8 0
Viewed 37

I have to split a column in MySQL table on each occurrence of a pattern say ":" and "," into multiple rows. Here is how the column data looks :-

                            Column_1
  page1:message1,page2:message2,page3:message3,page4:message4

I want to show data like this :-

Column_2  Column_1
page1     message1
page2     message2
page3     message3
page4     message4

I have tried looking to REGEXP_REPLACE() and SUBSTRING_INDEX() MySQL functions but couldn't form a query to achieve this.

Someone please help on this.

mariadb mysql database
2 Answers

If your MySQL version doesn't support windows function you can create an numbers table (with the maximum length of your string) as follows :

create table numbers (
  nr int
);

insert into numbers values (1),(2),(3),(4),(5);

Then follow a two step process.

First, divide your string with comma using:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1        
FROM test t
JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1

You will get a result like this:

new_col1
page1:message1
page2:message2
page3:message3
page4:message4

Second, divide the rows using : as below:

select SUBSTRING_INDEX(new_col1,':',1) as page,
       SUBSTRING_INDEX(new_col1,':',-1) as message
from (   SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1        
         FROM test 
         JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1 
  ) as t2;

Final result:

page  message
page1 message1
page2 message2
page3 message3
page4 message4

https://dbfiddle.uk/acWncQ8y

Ergest Basha0

You can use a cte to get a row for each occurence of a row with an index

WITH RECURSIVE expand (column1, n) AS (
select column1, 1 from
   (select
     'page1:message1,page2:message2,page3:message3,page4:message4' as column1) as 
      table1
      UNION ALL
      select column1 , n+1 from expand where n < CHAR_LENGTH(column1) - 
        CHAR_LENGTH(REPLACE(column1,',',''))
    )

This will give you a result as

column1                                                        n
page1:message1,page2:message2,page3:message3,page4:message4    1
page1:message1,page2:message2,page3:message3,page4:message4    2
page1:message1,page2:message2,page3:message3,page4:message4    3

with that you can now just grab the n-th occurence of the part delimited with , and just split that part into two columns

 Select 
 SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',1) as page,
 SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',-1) 
 as message from expand

and you get

page    message
page1   message1
page2   message2
page3   message3
overflowed0
Related