ordering search results excluding certain words

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!

TweetBacks
Comments
todd sharp's Gravatar 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
Joshua's Gravatar 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
todd sharp's Gravatar 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
todd sharp's Gravatar 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
Joshua Cyr's Gravatar 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
Joshua Cyr's Gravatar Todd can you take a look now? I think this should help. :-)
# Posted By Joshua Cyr | 9/6/07 9:32 AM
SciFi Vixen's Gravatar 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

NAVIGATION

Home
About Me

RSS


Search

Subscribe

Enter your email address to subscribe to this blog.

Recent Entries

Inception Plot Questions
Random Chuck Norris Fact Generator With A Twist
Virtual Currency for Buses
Applying ColdFusion Security Patches Gotcha
Privacy, Walled Gardens, Standards and Our Future

Recent Comments

FireFox 3.6 KTML Editor Fix
rock guitar said: Is the ktml realy discontinued for dreamweaver [More]

FireFox 3.6 KTML Editor Fix
Joshua said: Hi Wayne, I just tried opening the editor in IE from the link you supplied. I was able to open the ... [More]

FireFox 3.6 KTML Editor Fix
wayne said: Hi everybody, thanks for your reply joshua. I still have a bug for ktml in asp for a link properties... [More]

Inception Plot Questions
said: [More]

Inception Plot Questions
Taylor said: Hey there Josh, Great questions all. You seem to think this is more of a Jacob's Ladder equivalent ... [More]

Calendar

Sun Mon Tue Wed Thu Fri Sat
   1234
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30   

Archives By Subject

blogs (31) [RSS]
books (4) [RSS]
Crazy (39) [RSS]
DIY (8) [RSS]
Flex (3) [RSS]
games (10) [RSS]
GRRR (13) [RSS]
Ideas (11) [RSS]
Local (14) [RSS]
LOLpics (2) [RSS]
money (9) [RSS]
music (3) [RSS]
Personal (27) [RSS]
Photos (8) [RSS]
Politics (8) [RSS]
Projects (22) [RSS]
Review (19) [RSS]
RPM (9) [RSS]
Spam (16) [RSS]
Technology (68) [RSS]
Testing (3) [RSS]
TV (15) [RSS]
video (32) [RSS]
Web Dev (224) [RSS]
World of Warcraft (16) [RSS]