How to count from split string in mysql like this example

Question

how to manipulate data just look like this example in mysql

e.g.:

icd	diagnose
A00 1	diagnoseA00 1
B34 2;A00 9;A39 5	diagnoseB34 2;diagnoseA00 9;diagnoseA39 5

into

icd	diagnose
A00 1	diagnoseA00 1
B34 2	diagnoseB34 2
A00 9	diagnoseA00 9
A39 5	diagnoseA39 5

I was trying to use substring_index but it just took first string(1) or last string(-1) like table below

icd	diagnose
A00 1	diagnoseA00 1
B34 2	diagnoseB34 2;diagnoseA00 9;diagnoseA39 5

Answer 1

I think you can use the explode() function on icd and diagnose and then merge the results with the return data

Read more https://www.php.net/manual/en/function.explode.php

Answer 2

Something like this:

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;

One wonderful thing I found about MariaDB is that it has a built-in sequence engine. This makes some operation easier particularly on what you're trying to do. Your idea of using SUBSTRING_INDEX() is one of the right idea to tackle such task. However, it's not as straight forward as just with 1 SUBSTRING_INDEX() nor it doesn't require any other operation. In your case, you also need CASE expression. Add DISTINCT to return unique combination of extracted icd and diagnose value and you've got your expected output.

Demo fiddle

Answer 3

You can solve this with substring_index(). Check out my db<>fiddle for this.

The only condition for this code to work fine is - you should have same number of sub-strings in both the fields on a row. Otherwise this would break.

Sample:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.diagnose, ';', n.n), ';', -1) value1,
SUBSTRING_INDEX(SUBSTRING_INDEX(t.icd, ';', n.n), ';', -1) value2
  FROM t t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.diagnose) - LENGTH(REPLACE(t.diagnose, ';', '')));