sql - Custom ORDER BY to ignore 'the'

Question

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.

Answer 1

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