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

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.

10 Comments

Allison February 15th, 2012 at 9:37 pm

Awesome, works great. Thank you!

I was thinking I would have to add a column or modify values in the DB, but I’m glad I didn’t.

yekta January 21st, 2010 at 6:19 pm

Thats really great!

How could you do it with Rails using Sphinx (Thinking-Sphinx) They have stopwords but they aren’t really working for me

Andreas Lund July 24th, 2007 at 6:38 pm

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

Then it won’t complain.

Alex April 29th, 2007 at 9:19 pm

Beautiful query. Absolutely saved the day.

Thanks so much!
Alex

jessica February 3rd, 2007 at 12:44 pm

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

Basti November 22nd, 2006 at 11:54 am

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?

Matt November 21st, 2006 at 6:06 pm

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?

Matt November 21st, 2006 at 5:42 pm

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?

Marc October 25th, 2006 at 11:54 pm

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

Add a Comment