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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jxn
Forum Newbie
Posts: 22
Joined: Wed Jul 13, 2005 4:33 pm

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

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
jxn
Forum Newbie
Posts: 22
Joined: Wed Jul 13, 2005 4:33 pm

Post 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.
jxn
Forum Newbie
Posts: 22
Joined: Wed Jul 13, 2005 4:33 pm

Post by jxn »

also, is there a good OR syntax that works with this, so that I can disinclude both 'The ' and 'the ' and 'THE ' ?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

ORDER BY TRIM(LEADING 'the ' FROM LOWER(name))
jxn
Forum Newbie
Posts: 22
Joined: Wed Jul 13, 2005 4:33 pm

Post by jxn »

timvw wrote:

Code: Select all

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