UsefulConcept

I had an interesting sorting issue.   What I need to do is output in alphabetical order the names of various things.  Lets say bars in town.  Now many bars have the word "the" in them.  We don't want them in the T area though. 

For example i have data shown in no specific order:

Green Monkey
The Blue Mermaid
The Press Room
The Coat of Arms
Sandpiper

Doing a traditional query we would see them sorted and all of the THE names would be at the end.

Select LocationName
From LocationTBL
Order By LocationName

What I want, though is for them to be sorted by their name, excluding the word THE.

The Blue Mermaid
The Coat of Arms
Green Monkey
The Press Room
Sandpiper

I figured this is something I can do in SQL, but I am not an SQL wizard and thus far I can find no functions that simply let me replace one word.  It would be handy to do something like:

Select LocationName
From LocationTBL
Order By replace(LocationName, 'the ', '')

Has anyone done this before?  Any suggestions?  My alternative is to go through all the data, and remove the word THE then at the same time add a column 'containsthe' and value of true.  Then prepend that in the display when true.  Sort of a kludge though.  Oh, and the client in this case is pretty picky and isn't bending on the display.

UPDATE:

I take it back, it really was that easy.  Turns out mysql has a replace function, I just didn't see it the first several searches.  Sweet!

Like this entry? Subscribe to my blog.