Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search