CODEWITHSUNDEEP
mysql
makensen
makensen

Reputation: 5

Split mysql column into multiple rows

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

Upvotes: 0

Views: 109

Answers (1)

user16351167
user16351167

Reputation:

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

Upvotes: 1

Related Questions