SQL: CASE in ORDER BY

[Jetsam]

i need to order by part of a varchar field, such as the example below does for oracle:

select name 
from something.somethingelse 
order by decode(upper(substr(name,1,3)),'THE',substr(name,5),name)

i've searched and searched yet i cannot find how to do this for access. ive done this a million times for oracle with a decode in the order by, but how do i use the case statement for access? the following does not work:

select name
from something
order by
 case when Ucase(Mid(name,1,3)) = "THE"  then Mid(name,4,Len(name)-4)
 else name
end

thanks for any help!

 

[Mile-O]

SELECT BookID, Book
FROM tblBooks
ORDER BY IIF(Left$(Book,4) = "The ",Mid$(Book,5),Book);


I fixed the error in your SQL too since you need to check for a space after THE otherwise books called "THEY CAME FROM THE DESERT" or "THESE DAYS", etc would be sorted incorrectly.

 

[Jetsam]

SELECT BookID, Book
FROM tblBooks
ORDER BY IIF(Left$(Book,4) = "The ",Mid$(Book,5),Book);


I fixed the error in your SQL too since you need to check for a space after THE otherwise books called "THEY CAME FROM THE DESERT" or "THESE DAYS", etc would be sorted incorrectly.

thank you so much! i couldnt find this info anywhere. you know how annoying the msdn is to try to find something very specific.

id hug u if you were here

oh, yeah i fixed the space issue on mine but figured it wasnt worth updating here since it was the logic i was more worried about.

thanks again!

adding some keywords to help people find this when searching the forums.
keywords: case, switch, order by, if, then, iif, ordering by part of a a string, conditional order by

 

[Mile-O]

id hug u if you were here

Hope you're not a guy, then.