How split multiples subvalues with multiples SubIndex in columns mysql?

Question

CREATE TABLE tablename (id INT,C1 text);

INSERT INTO tablename VALUES
(1, '[AU 1] string 1; [AU 2] string 2; [AU 3] string 3.1; string 3.2; [AU 4] string 4.1; string 4.2; [AU 5] string 5'),
(2, '[AU 1; AU 2] string 1'),
(3, '[AU 1] string 1; [AU 2] string 2');

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6);

As close as I got by following the examples of '@fthiella' and '@RGarcia'.

Please see fiddle here.

The result I get is different than expected in "I want output like this:"

I want output like this

| ID | AU   | ORG |
| 1  |[AU 1]|string_1|
| 1  |[AU 2]|string_2|
| 1  |[AU 3]|string_3.1|
| 1  |[AU 3]|string_3.2|
| 1  |[AU 4]|string_4.1|
| 1  |[AU 4]|string_4.2|
| 1  |[AU 5]|string_5|
| 2  |[AU 1; AU 2]|string_1|
| 3  |[AU 1]|string_1|
| 3  |[AU 2]|string_2|

in the first place https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad don't store the data in delimiters columns, also semocolon ist the deliter you can't use it in that way. last use json if you really want to have such delimterd columns — nbk, Apr 03 '21 at 19:26
@nbk, grateful for the suggestion, but the base is not available in jason and transforming it would take a lot more work — cnauber, Apr 03 '21 at 19:41
you should switch to a stored procidure or function and this wikll take some effort, because you have the same delimiter in different capacity — nbk, Apr 03 '21 at 19:52

score 1 · Accepted Answer · answered Apr 03 '21 at 20:56

WITH RECURSIVE
cte1 AS ( SELECT id, 
                 TRIM(TRAILING ';' FROM TRIM(SUBSTRING_INDEX(C1, '[', 2))) one_group,
                 SUBSTRING(C1 FROM LENGTH(SUBSTRING_INDEX(C1, '[', 2))) slack,
                 1 ordinality_au
          FROM test
          UNION ALL
          SELECT id, 
                 TRIM(TRAILING ';' FROM TRIM(SUBSTRING_INDEX(slack, '[', 2))),
                 SUBSTRING(slack FROM LENGTH(SUBSTRING_INDEX(slack, '[', 2))),
                 ordinality_au + 1
          FROM cte1
          WHERE LOCATE('[', slack) ),
cte2 AS ( SELECT id,
                 CONCAT(SUBSTRING_INDEX(one_group, ']', 1), ']') AU,
                 TRIM(SUBSTRING_INDEX(one_group, ']', -1)) ORG,
                 ordinality_au
          FROM cte1 ),
cte3 AS ( SELECT id, 
                 AU,
                 ordinality_au,
                 SUBSTRING_INDEX(ORG, ';', 1) ORG,
                 TRIM(TRIM(LEADING ';' FROM TRIM(LEADING SUBSTRING_INDEX(ORG, ';', 1) FROM ORG))) slack,
                 1 ordinality_org
          FROM cte2 
          UNION ALL
          SELECT id, 
                 AU,
                 ordinality_au,
                 SUBSTRING_INDEX(slack, ';', 1),
                 TRIM(TRIM(LEADING ';' FROM TRIM(LEADING SUBSTRING_INDEX(slack, ';', 1) FROM slack))),
                 ordinality_org + 1
          FROM cte3
          WHERE TRIM(slack) != '' )
SELECT id,
       AU, 
       ORG
FROM cte3
ORDER BY id, ordinality_au, ordinality_org;

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=a3258f8f1cd92eca0c480ea6673f13f1

How split multiples subvalues with multiples SubIndex in columns mysql?

1 Answers1