How to sort MySQL results ignoring definite and indefinite articles (the, a, an) July 14, 2006

Posted by Slobodan Kovacevic in : Resources and Links , trackback

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.

Comments»

1. Marc - October 25, 2006

This was exactly what I was looking for. Thanks so much.

2. Matt - November 21, 2006

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?

3. Matt - November 21, 2006

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?

4. Basti - November 22, 2006

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 TitleSort

Does that works?

5. jessica - February 3, 2007

Wow, this worked immediately with a copy/paste, and it was exactly what I was looking for. Thanks so much for posting this!

6. Alex - April 29, 2007

Beautiful query. Absolutely saved the day.

Thanks so much!
Alex

7. Andreas Lund - July 24, 2007

With MySQL instead of
WHERE TitleSort LIKE “m%”
use
HAVING TitleSort LIKE “m%”

Then it won’t complain.