By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
icd VARCHAR(255),
diagnose TEXT);
INSERT INTO mytable VALUES
('A00 1','diagnoseA00 1'),
('B34 2;A00 9;A39 5','diagnoseB34 2;diagnoseA00 9;diagnoseA39 5');
Records: 2 Duplicates: 0 Warnings: 0
SELECT * FROM mytable;
SELECT * FROM seq_1_to_20;
| icd | diagnose |
|---|---|
| A00 1 | diagnoseA00 1 |
| B34 2;A00 9;A39 5 | diagnoseB34 2;diagnoseA00 9;diagnoseA39 5 |
| seq |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
SELECT DISTINCT
CASE WHEN icd LIKE '%;%'
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(icd,';',seq),';',-1)
ELSE SUBSTRING_INDEX(icd,';',seq) END AS icd2,
CASE WHEN diagnose LIKE '%;%'
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(diagnose,';',seq),';',-1)
ELSE SUBSTRING_INDEX(diagnose,';',seq) END AS diagnose2
FROM mytable m
CROSS JOIN seq_1_to_20 s;
| icd2 | diagnose2 |
|---|---|
| A00 1 | diagnoseA00 1 |
| B34 2 | diagnoseB34 2 |
| A00 9 | diagnoseA00 9 |
| A39 5 | diagnoseA39 5 |