Sort by second word?

[trendMM]

Hi,

I have a quick question, not sure if this is possible or not. I'm putting together a DB that holds a list of bands along with the info for each individual band. Everything works great both posting and viewing on the public frontend.

However, a lot of bands these days have "The" in the beginning of their name, I was wondering if there was anyway I could use the sort function by their name as usual, but possible have it strip "The" from the beginning of the name in the sort function.

For example.

The Graduate would normally come up under "T". I would like it to still say "The Graduate", but have it listed under "G".

I know this could possibly be something simple and I'm sorry if it's a dumb question. I just have never tried anything like this before and I can't seem to find anything no matter what I search, because I don't know what exactly I'm searching for.

If anyone could possibly help me out with a possible function or something that would be great.

Thanks in advance!

[coldwerturkey]

this isn't the best way, but you could put the bands into an array make the value the title, than use str_replace() to strip 'The' from the title and use that as the key to sort by..

[Lars_Berg]

order by 
   case when upper(substring(bandname from 1 for 4)) = 'THE ' then 
                     substring(bandname from 4) 
            else bandname end

depending on the DBMS you use you may need to change the substring invocation as the syntax differs amomg different systems.