mysql - Split delimited string value into rows

Question

Some external data vendor wants to give me a data field - pipe delimited string value, which I find quite difficult to deal with.

Without help from an application programming language, is there a way to transform the string value into rows?

There is a difficulty however, the field has unknown number of delimited elements.

DB engine in question is MySQL.

For example:

Input: Tuple(1, "a|b|c")

Output:

Tuple(1, "a")
Tuple(1, "b")
Tuple(1, "c")

Answer 1

It may not be as difficult as I initially thought.

This is a general approach:

1. Count number of occurrences of the delimiter length(val) - length(replace(val, '|', ''))
2. Loop a number of times, each time grab a new delimited value and insert the value to a second table.