mysql query to parse string with unknown length

Question

Sample pattern is as shown in example, i want to separate values before and after : (colon)

Sample string pattern: 23:4,13:6^12:1^15:3^45:6,99:2,64:1^.....^

Note: between caps number commas may vary for e.g

 23:4,13:6^  - 1comma
 ^12:1^   - no comma
 ^45:6,99:2,64:1^  - 2 commas 

Expected output:

 ColumnA    ColumnB
 ------------------
    23        4
    13        6
    12        1
    15        3
    45        6

Answer 1

You can simply replace the delimiter characters with spaces.

SELECT REPLACE(REPLACE(REPLACE(colname, ',', ' '), ':', ' '), '^', ' ')
FROM yourTable