John Doe's personal blog

Something came up in IRC today, I thought I found a pretty nice solution to it, thought I’d share it here. A guy has a table in his database that contains ‘prefixes’. It looks like this:

Given the phone number “18005551234”, he’d like to find the longest valid prefix. He had suggested to me that he was considering a stored procedure which loops, character-by-character, through the input looking for the longest match. Instead, I recommended he use ‘replace’ to trim the ‘prefixes’ off of the input, and let an ‘order by’ tell him which of those actually resulted in the shortest remaining string (and thus, was the longest match).

The query I ended up with goes something like this:
select id from prefixes order by length(replace('18005558355', number, '')) asc limit 1;
Given a better understanding of mysql’s ‘replace()’, we ended up using this instead – note the identities are renamed to reflect his actual sytem:
SELECT id, prefix from routes where locate(prefix, '18005558355') = 1 order by length(prefix) desc limit 1;

Is there a better way? Below the fold is a quick (postgresql) “dump” of the database in question so you can come up with your own proposals.

Let’s be honest – there aren’t many of you reading this, and there’s just not that much of me to go around, so if you read blog.joeysmith.com, I’d like to hear from you regarding what sort of topics I should write about. Here are some ideas, but feel free to submit your own:

• Information Visualization – I have at least a half-dozen articles on the subject laying around in various states of (in-)completion.
• Parsing Expression Grammars – how to stop using regular expressions and use a parser instead
• PHP WTFs, tricks and anecdotes – a decade of participation in FreeNode’s ##PHP channel has given me some great material in the “WTF” category
• Data Structures and their Algorithms – this would probably consist of discussing a particular data structure, showing an implementation in a couple of different languages (Maybe “JS, PHP, Python…???”)
• Book reviews

This week’s Trek West 5 podcast will be discussing The Measure of a Man, which contains a (brief) mention of this sonnet, one of the few I’ve actually memorized. Here it is in its entirety:

When in disgrace with fortune and men’s eyes I all alone beweep my outcast state,
And trouble deaf heaven with my bootless cries, And look upon myself, and curse my fate,
Wishing me like to one more rich in hope, Featured like him, like him with friends possessed,
Desiring this man’s art, and that man’s scope, With what I most enjoy contented least;
Yet in these thoughts my self almost despising,
Haply I think on thee, and then my state, Like to the lark at break of day arising
From sullen earth, sings hymns at heaven’s gate; For thy sweet love remembered such wealth brings
That then I scorn to change my state with kings.

Wikipedia brings us an entry on Kryptos, a sculpture that stands outside the CIA headquarters in Langley, VA which contains 4 puzzles – three have been solved, the 4th continues to stymie everyone who attempts to solve it.

Watching Star Trek: The Next Generation for my podcast reintroduced me to the poem “The Spell of the Yukon”, by Robert W. Service. This poem often makes me think of my childhood in Wyoming where I would spend hours and hours getting lost to the world in the alley behind our house…

It’s the great, big, broad land ‘way up yonder,
It’s the forests where silence has lease;
It’s the beauty that thrills me with wonder,
It’s the stillness that fills me with peace.

The rest of The Spell of the Yukon below the fold…