Do you have an output that is in comma or pipe-delimited format and you want to go digging in a MySQL database for the data that corresponds to that output? Don’t feel like cutting and pasting and/or scripting in a language better suited for the task and just want it all to happen in MySQL SQL?
I highly advise against this.
But if you insist, here’s an example:
select * from (select substring_index(substring_index( substring_index( substring_index( '1234555 | 3c4004d2-b2a1-4a0c-face-edaac27ef695 | 23 1234556 | 03d607a3-c2d2-face-870f-26bb551d6da7 | 12 2344464 | face7729-a1e1-4df8-9dd1-ac73c3383e4a | 19 2344344 | 9d49fc59-face-47a9-801d-49ffed1dfb56 | 91', '\n', list.i), '\n', -1), '|', 2), '|', -1) as uuid from (select t.i * 2 + o.i + 1 as I from (select 0 as i union select 1) as o -- ones cross join (select 0 as i union select 1) as t -- twos ) as list order by list.i) as uuids;
Great. How does it work?
Let’s start with the subselect for
select 0 as i union select 1 returns a set of rows whose contents are in a single column, with values 0 and 1. The
cross join of two copies of this query creates all permutations of 0 and 1 from the
o (ones) select with 0 and 1 from the
t (twos) select. I did this as binary digits because 4 was an even power of two, but you could do base ten with:
from (select 0 as i union select 1 union select 2 union select 3...
With the permutations of rows, I just add
t.i * 2 +
o.i to get values
11 in binary and then add 1 to them. This calculated value will be aliased as
i in the subselect
Getting the rows
substring_index(substring_index('multiline string goes here', '\n', list.i),'\n',-1) gets the string up to the
list.ith delimiter (in other words,
list.i rows) in the inner
substring_index and the gets the last occurrence between the
'\n' delimiters (last row) with the outer
substring_index with the
Getting a specific pipe-delimited field
uuid (second) pipe-delimited only requires repeated the
substring_index pattern but with
substring_index(substring_index(row returned from the above section), '|', 2), '|', -1). The
'|' and the
2 gets up to the second pipe and then the
'|' and the
-1 gets the last occurrence.
Don’t do this
This is a terrible idea for multiple reasons, not the least of which is that it was enough material for a blog post just trying to explain how it worked!