Yesterday, I spent a minute helping a coworker with a SQL problem: He needed to order the results by a varchar field, but not in the easily available lexical order. The order was set by the marketing folks, and our task was to make it work.

There are three ways to do this, and I'll talk about each one.

First, you can perform the sorting after the query is done, in whatever dynamic language you are using. This is probably bad for several reasons.

Second, you could create a table that contains the values to be sorted and a field that contains the sort order in numerical fashion. This is a pain.

Third: Use a scalar function to get things to fall into place. Observe:

Quick and easy.