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.