Friday, May 15, 2009

Challenge : Simulating Split

As programming languages and libraries become more complex, you don’t find the same sort of puzzling little challenges you used to in the old days.  One of my favorite questions to ask interviewees a long time ago was “Write the round() function when all you have is the floor function.”  (One of many answers:  Add 0.5 to your number, and then execute floor() on it. Blah blah blah excepting for negative numbers and any other nitpicks, this is not the point of my post….)

Here’s the challenge I found today.  I’ve got a file pathname stored in a database string.  They come from a variety of sources so there’s no inherent pattern in them other than the usual http:// to start, separated by / delimiters.  Turns out that for a report I need a quick way to get just the file portion – so, just the last bit.

With a split() function I’d be fine.  This is one of those things that’s easy to do in something like awk where you just ask for the last field.

But I’m in MySQL, and unless I missed something, MySQL doesn’t have a split.  I found several examples of how to simulate it using a fixed number of nested substr() and locate(delim, string) calls.  That works – if you know ahead of time which bit you need, i.e. “I need the 4th field.”

Well, here I don’t know.  All I know is that I need the last one.

I spotted the quick and simple answer as soon as I saw it.  Anybody know what answer I saw?


steveo said...

If you have a string reversal and a length function:
substring(length(string)-locate('/', reverse(string)), length(string), string)

Duane said...

Exactly :). I love those "If you don't have function X can you simulate it with functions A B and C" sorts of problems.