0

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

makensen
  • 5
  • 5
  • What version are you using? –  Jul 28 '21 at 15:34
  • hi JS Bach, thanks for caring, the database is mariadb 10.5 on Ubuntu 18.04 – makensen Jul 28 '21 at 18:43
  • 1
    Does this answer your question? [MYSQL - Split Data Into Multiple Rows](https://stackoverflow.com/questions/5342629/mysql-split-data-into-multiple-rows) – Timothy Alexis Vass Jul 28 '21 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 Jul 28 '21 at 18:57

1 Answers1

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