Custom ORDER BY to ignore 'the'

mysql, sorting, sql

Solution

Best is to have a computed column to do this, so that you can index the computed column and order by that. Otherwise, the sort will be a lot of work.

So then you can have your computed column as:

CASE WHEN title LIKE 'The %' THEN stuff(title,1,4,'') + ', The' ELSE title END

Edit: If STUFF isn't available in MySQL, then use RIGHT or SUBSTRING to remove the leading 4 characters. But still try to use a computed column if possible, so that indexing can be better. The same logic should be applicable to rip out "A " and "An ".

Rob

Problem

I'm trying to sort a list of titles, but currently there's a giant block of titles which start with 'The '. I'd like the 'The ' to be ignored, and the sort to work off the second word. Is that possible in SQL, or do I have to do custom work on the front end? For example, current sorting: - Airplane - Children of Men - Full Metal Jacket - Pulp Fiction - The Fountain - The Great Escape - The Queen - Zardoz Would be better sorted: - Airplane - Children of Men - The Fountain - Full Metal Jacket - The Great Escape - Pulp Fiction - The Queen - Zardoz Almost as if the records were stored as 'Fountain, The', and the like. But I don't want to store them that way if I can, which is of course the crux of the problem.

Original source