I just fought with this for a little while, so this is here for my own reference:'
To order a returned dataset from a MySQL database, ignoring "A", "An", or "The" when sorting, you need to use the following query (assume that 'title' is the column you want to sort by):
SELECT * ,
IF (
left( title, 2 ) = 'a ' OR left( title, 4 ) = 'the ',
IF (
left( title, 2 ) = 'a', right( title, length( title ) -2 ) , right( title, length( title ) -4 )
), title
) AS at
FROM primarytexts
Posted by Darren James Harkness on Sunday, October 3, 2004 08:38 PM
Trackbacks...
Hey Darren,
I'm not sure, but I think MySQL has some flavour of SUBSTRING() that may make this a bit easier.
-- Posted by >> Wim » Tuesday, October 5, 2004 04:54 PMThis is actually a slightly better version:
SELECT * ,
-- Posted by >> Darren » Wednesday, October 27, 2004 11:42 PMIF (
left( title, 2 ) = 'a ' OR left( title, 4 ) = 'the ' OR left( title, 3 ) = 'an',
IF (
left( title, 2 ) = 'a' OR left( title, 2 ) = 'an', right( title, length( title ) -2 ) , right( title, length( title ) -4 )
), title
) AS sorttitle
FROM primarytexts
ORDER BY sorttitle ASC
Post a comment
* under no circumstances will your email address be traded for a sack of quarters. No-sirree.