A ‘Grrrr!’ moment

Imagine, if you will, you have a series of n dot separated strings, something like ‘foo.bar.bat.blah’, and you want to reduce it to the final term in the series, in this case ‘blah’. What does the SQL look like to that?

Seeing as there can be any number of strings before the last one, you end up doing something like this:

reverse(substring(reverse(col), 1, locate(‘.’, reverse(col)) – 1))

which: reverses the value of the column; locates the first ‘.’; creates a substring that contains everything up to the index before the ‘.’; then reverses the result. Cute, huh? Bleeeugh!

That’s when you find out that MySQL has a substring_index(str, delim, count) function that will do the same with:

substring_index(col, ‘.’, -1)

Hate.

Advertisements