Related Categories: Web Dev

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.

Comments (moderation on)

I was gonna say.... :)

I love replace() - MySql has the same function and it is invaluable...
# Posted By todd sharp | 9/5/07 5:26 PM
yea, all my google searches for sql functions kept bringing up lists of string functions that were not complete apparently. I finally did a search for mysql string functions and found it. I should have just tried the code. LOL The only thing I noticed is that it removed "the" just fine, but not the space after the and thus all the results were at the top. So I revised it to replace for "the " and all is well with the world.
# Posted By Joshua | 9/5/07 5:30 PM
Ah good catch...

You could also, if need be, nest replace() to handle multiple strings too...though that could get ugly...
# Posted By todd sharp | 9/5/07 9:45 PM
PS...just my friendly two cents...but the comments all kinda run together here on the blog - maybe you could use an <hr> or alternating row colors? Just a thought.
# Posted By todd sharp | 9/5/07 9:46 PM
there is actually a light gray bg around each comment with white padding... though it is very very slight. Probably not perceptible on some screens. Thanks for the feedback!
# Posted By Joshua Cyr | 9/6/07 9:12 AM
Todd can you take a look now? I think this should help. :-)
# Posted By Joshua Cyr | 9/6/07 9:32 AM
It was really easy because we gave you the pad thai with magical tofu in it and it made you a wizard! (Good thing Jenny doesn't like tofu, she would have used her power for evil.)
# Posted By SciFi Vixen | 9/6/07 9:59 AM

Sponsors


Savvy Content Manager