labs.dagensskiva.com

At dagensskiva.com we don’t necessarily want to list artists by the first letter of their name, e.g. we want The White Stripes to be listed under W, not T. The following MySQL ORDER clause will perform such as sort.

SELECT artist FROM bands ORDER BY IF(LEFT(LOWER(artist), 4) = 'the ', LOWER(RIGHT(artist, LENGTH(artist)-4)), LOWER(artist)) ASC

Which will also ignore the case of the first letter after the The when sorting by transposing down to lowercase. I’m forcing to a hex conversion to deal with åäö, not sure how that will work in character sets other than UTF8.

My feeling is that there is a cleaner way to do this - do let me know if there is a prettier way of doing this.

Edit (01/01/2008): As Johan Dahlin pointed out, locale based sorting is not the work of the developer, but the database. Thanks for the help!