Split mysql column into multiple rows

Question

I have below structure in mysql database:

EMAIL       PERMISSIONS
a@b         {app1: perm1; perm2; perm3};{app2: perm4; perm5; perm6}

I would like to use a query that will return the data in below format, in 3 columns:

a@b         app1        perm1
a@b         app1        perm2
a@b         app1        perm3
a@b         app2        perm4
a@b         app2        perm5
a@b         app2        perm6

Can be more than 3 permissions per each application.

Thank you

hi JS Bach, thanks for caring, the database is mariadb 10.5 on Ubuntu 18.04 — makensen
– makensen, Commented Jul 28, 2021 at 18:43
Does this answer your question? MYSQL - Split Data Into Multiple Rows — Timothy Alexis Vass
– Timothy Alexis Vass, Commented Jul 28, 2021 at 18:48
hi Timothy.. I'm not allowed to use stored procedures for some reasons, that's why ideally I'll find a query to that.. Failed until now, only using substring_index seems not to sufficient, that's why I asked for help.. — makensen
– makensen, Commented Jul 28, 2021 at 18:57

user16351167 · Accepted Answer · 2021-07-29 08:42:57Z

1

Schema (MySQL v8.0)

CREATE TABLE my_bad_data
(email VARCHAR(12) NOT NULL
,permissions VARCHAR(250) NOT NULL
);

INSERT INTO my_bad_data VALUES
('a@b','{app1: perm1; perm2; perm3};{app2: perm4; perm5; perm6}');

Query #1

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 50
)
SELECT DISTINCT
       a.email
     , a.app
     , SUBSTRING_INDEX(SUBSTRING_INDEX(a.perms,';',n),';',-1) perm
  FROM 
     ( SELECT DISTINCT 
              email
            , SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(permissions,'};{',n),'};{',-1),'{',''),'}',''),':',1)app 
            , SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(permissions,'};{',n),'};{',-1),'{',''),'}',''),':',-1)perms 
         FROM my_bad_data
            , cte
      ) a
   JOIN cte;

email	app	perm
a@b	app1	perm1
a@b	app2	perm4
a@b	app1	perm2
a@b	app2	perm5
a@b	app1	perm3
a@b	app2	perm6

View on DB Fiddle

answered Jul 29, 2021 at 8:42

user16351167

Sign up to request clarification or add additional context in comments.

1 Comment

makensen Over a year ago

Wow, JS Bach... wish you all the best, thanks, it's perfect

Collectives™ on Stack Overflow

Split mysql column into multiple rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related