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

Answer 2

Something like:

ORDER BY IF(LEFT(title,2) = "A ",
            SUBSTRING(title FROM 3),
            IF(LEFT(title,3) = "An ",
              SUBSTRING(title FROM 4),
              IF(LEFT(title,4) = "The ",
                 SUBSTRING(title FROM 5),
                 title)))

But given the overhead of doing this more than a few times, you're really better off storing the title sort value in another column...

Answer 3

I think you could do something like

ORDER BY REPLACE(TITLE, 'The ', '')

although this would replace any occurrence of 'The ' with '', not just the first 'The ', although I don't think this would affect very much.