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 list
:
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 00
through 11
in binary and then add 1 to them. This calculated value will be aliased as i
in the subselect list
.
Getting the rows
substring_index(substring_index('multiline string goes here', '\n', list.i),'\n',-1)
gets the string up to the list.i
th 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 -1
argument.
Getting a specific pipe-delimited field
Getting the 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!