Wednesday, November 25, 2009

ORDER by title in mysql, ignoring the word “The”

Often time when you want to sort your sql return record by title, but the title starts with the word "The", for example "The Mummy" and we wants to sort by "Mummy" instead.
There are 2 solutions I have found:
1. Create a new field on the fly and sort by using the new field.
Select *, str_replace(title, 'The ', '') as sort_title ORDER BY sort_title

2. For better query performance, create a new column in the table ('sort_title') that is same as the title column but without a prefix of "The " or "A".

Some more info can be found here: http://stackoverflow.com/questions/695664/how-do-you-order-by-title-in-mysql-ignoring-the-word-the

No comments: