How to sort MySQL results ignoring definite and indefinite articles (the, a, an)
Posted By Slobodan Kovacevic on July 14, 2006
We recently had a problem - we needed to sort MySQL result based on Title column, but while sorting we needed to ignore both definite and indefinite articles (i.e. ‘a’, ‘an’, ‘the’).
Although this is an frequent request (people mostly want to ignore articles when sorting), we didn’t find a lot of solutions. Those which we found usually involved suggestions like “just put ‘the’ in the end when inputting data” or “add another column in which you can enter whatever you want and use it to sort”… All those solutions work and can be used, but also all of them are unacceptable because they unnecessarily ask from user to perform additional operations.
Finally we stumbled onto a thread on Site point MySQL: Proper sorting of a column which has a solution in a very last post.
Here is the query (slightly modified/formatted) that performs sorting ignoring articles:
SELECT Title,
CASE WHEN SUBSTRING_INDEX(Title, ' ', 1)
IN ('a', 'an', 'the')
THEN CONCAT(
SUBSTRING(Title, INSTR(Title, ' ') + 1),
', ',
SUBSTRING_INDEX(Title, ' ', 1)
)
ELSE Title
END AS TitleSort
FROM music
ORDER BY TitleSort
What it basically does is that beside actual Title column it also adds a second column called TitleSort which contains Title without articles and so it can be used for proper sorting.
The query logic is simple. First determine if the first word (i.e. everything from start to first space) of the Title is ‘a’, ‘an’ or ‘the’. If it is, then it will move it to the end of the string (for example, “The Best Of” will become “Best Of, The”). Otherwise it won’t touch Title and TitleSort will be exactly the same. Of course in the end it sorts everything according to values in TitleSort.
This was exactly what I was looking for. Thanks so much.
Thanks a lot! I saw another method for this that unfortunately didn’t work correctly. How long did it take you to figure this one out?
How would you only show titles that only start with M in such a way as they would result in a list like.
Man, The
Mary
Misfits, The
Mops
I can’t do
WHERE TitleSort LIKE “m%”
because mysql complains that TitleSort is not a column. Any way around that?
You can simply repeat CASE statement in WHERE clause. Something like this:
SELECT Title, CASE WHEN SUBSTRING_INDEX(Title, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT( SUBSTRING(Title, INSTR(Title, ' ') + 1), ', ', SUBSTRING_INDEX(Title, ' ', 1) ) ELSE Title END AS TitleSort FROM music WHERE CASE WHEN SUBSTRING_INDEX(Title, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT( SUBSTRING(Title, INSTR(Title, ' ') + 1), ', ', SUBSTRING_INDEX(Title, ' ', 1) ) ELSE Title END LIKE "m%" ORDER BY TitleSortDoes that works?
Wow, this worked immediately with a copy/paste, and it was exactly what I was looking for. Thanks so much for posting this!
Beautiful query. Absolutely saved the day.
Thanks so much!
Alex
With MySQL instead of
WHERE TitleSort LIKE “m%”
use
HAVING TitleSort LIKE “m%”
Then it won’t complain.