Page 1 of 1

is there a way to get ORDER BY to ignore "The%"?

Posted: Thu Jul 28, 2005 7:49 pm
by jxn
Hi there,
I'm working on an application that sorts CDs by artist name, and there are so many The* bands, it's worthless to sort by their titles reasonably, and I was just wondering if there is any way to get my order by clause to ignore The*/the*/THE*/etc when they begin the string returned through mysql_fetch_assoc or mysq_fetch_row ? If I must do it through some php hack, I'm willing to work with that, too, but I'd prefer to have the whole string returned as a title, I just want the The ignored by the order statement... any help? ...ideas?

Posted: Thu Jul 28, 2005 8:53 pm
by timvw
http://dev.mysql.com/doc/mysql/en/string-functions.html

Code: Select all

ORDER BY TRIM(BOTH 'the' FROM name)

PS: I've seen that most naming systems use names like: Dire Straits, the

Posted: Thu Jul 28, 2005 10:17 pm
by jxn
I had already read that portion of the manual and tried experimenting with TRIM(), LTRIM(), REPLACE(), and a half dozen other features, but I kept getting syntax errors/etc, I couldn't find many meaningful examples until your example... I had been putting in ORDER BY `artist` TRIM(LEADING 'The' FROM `artist`) or similar, never understanding that the TRIM() functions itself as the object, not merely a modifier... now everything seems to make more sense; Thanks!

For future reference, in case anybody searches and finds this, I actually needed to use TRIM(LEADING 'The ' FROM `artist`) to get alpha to sort out right, because otherwise mysql treats the whitespace as a character and all your former The% results will be ordered before everything else.

Thanks again!

Also, for reference, I used LEADING instead of BOTH because a lot of artist names in the database are very close, for example, I have a band in the database called "The Planet The" and a band called "The Planet", so I wouldn't want that extra "The" ignored or they'd be slightly out of alphabetical order, probably.

Posted: Thu Jul 28, 2005 10:23 pm
by jxn
also, is there a good OR syntax that works with this, so that I can disinclude both 'The ' and 'the ' and 'THE ' ?

Posted: Thu Jul 28, 2005 10:36 pm
by timvw

Code: Select all

ORDER BY TRIM(LEADING 'the ' FROM LOWER(name))

Posted: Thu Jul 28, 2005 10:58 pm
by jxn
timvw wrote:

Code: Select all

ORDER BY TRIM(LEADING 'the ' FROM LOWER(name))
smart. Thanks, much!