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?
split-string-into-rows.sql-- Splits a comma-separated string (AKA "SET"), $strlist, and returns the element (aka substring) matching the provided index, $i.
MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values. For example, the following statement returns one-based index of value C in string A,B,C,D . If the given value is not found, FIND_IN_SET() function returns 0 .
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.
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 )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With