Converting delimited string to multiple values in mysql

Question

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?

Answer 1

Interesting.

I can't think of a clean and tidy solution, but I could offer some ideas.

I guess you could combine UNION with the following:

http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

If you know the maximum number of items in any row, then you could do a union on the results .. something like:

(SELECT col1, SPLIT_STR(col2, ',', 1) c2 from tbl)
UNION (SELECT col1, SPLIT_STR(col2, ',', 2) c2 from tbl where col2 like '%,%')
UNION (SELECT col1, SPLIT_STR(col2, ',', 3) c2 from tbl where col2 like '%,%,%')
...

I'm not very up on mysql stored procedures, but perhaps you could do some clever stuff with loops in order to make this more dynamic.

I hope this points you in the right direction - ?

Answer 2

This is called walking a string. Here's an example of how you might do it with the specs provided:

You'll need to create a table which contains as many integers as the length of the field + 1. So if the field's length is 255, you will need 256 records which just contain a single number from 0-255.

int_table:

+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

Next, you will need a query which joins on this table and checks if a comma exists in that location or not. (I called your table legacy_table with the fields client and items, respectively.)

select 
  legacy_table.client, 
  substring(
    legacy_table.items, 
    int_table.i + 1, 
    if(
      locate(',', legacy_table.items, int_table.i + 1) = 0, 
      length(legacy_table.items) + 1, 
      locate(',', legacy_table.items, int_table.i + 1)
    ) - (int_table.i + 1)
  ) as item
from legacy_table, int_table
where legacy_table.client = 'xyz001'
  and int_table.i < length(legacy_table.items)
  and (
    (int_table.i = 0) 
    or (substring(legacy_table.items, int_table.i, 1) = ',')
  )

It may not be efficient enough for you to actually use it, but I thought I'd present it as an example just so you know what is available.

Answer 3

You can do this with a Numbers or Tally table which contains a sequential list of integers:

Select Substring(T.List, N.Value, Locate(', ', T.List + ', ', N.Value) - N.Value)
From Numbers As N
    Cross Join MyTable As T
Where N.Value <= Len(T.List)
    And Substring(', ' + T.List, N.Value, 1) = ', '

In the above case, my Numbers table is structured like so:

Create Table Numbers( Value int not null primary key )