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

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.

CodePudding user response：

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

CodePudding user response：

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