Converting delimited string to multiple values in mysql
- by epo
I have a mysql legacy table which contains an client identifier and a list of items, the latter as a comma-delimited string. E.g. "xyz001", "foo,bar,baz". This is legacy stuff and the user insists on being able to edit a comma delimited string.
They now have a requirement for a report table with the above broken into separate rows, e.g.
"xyz001", "foo"
"xyz001", "bar"
"xyz001", "baz"
Breaking the string into substrings is easily doable and I have written a procedure to do this by creating a separate table, but that requires triggers to deal with deletes, updates and inserts. This query is required rarely (say once a month) but has to be absolutely up to date when it is run, so e.g. the overhead of triggers is not warranted and scheduled tasks to create the table might not be timely enough.
Is there any way to write a function to return a table or a set so that I can join the identifier with the individual items on demand?
