Sort a Query Based on Omitting A Word

Question

if I have the following result sets:

id, name 
1, The Popper
2, Brittany
3, Fledgler
4, The Nightingale

How do I sort them by name so that it disregards the "The" word and sorts the as follows:

id, name
1, Britanny
2, Fledler
3, The Nightingale
4, The Popper

Thanks in advance. I am using MySQL

Answer 1

 SELECT id,title,
    CASE WHEN LOWER(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 books
ORDER BY TitleSort

Answer 2

Try:

SELECT id, name
FROM myTable
ORDER BY TRIM(LEADING 'The ' FROM name)

More info here.

Answer 3

Use this --> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim to remove the "The" from the beginning of the keyword in the Order by clause.

Answer 4

There are several answers that suggest "live" trimming, which will work fine.

However, if performance is important (huge lots of rows) consider having a separate column title_sort that contains the title with The and other disregarded words already removed.

That way, the database engine can make full use of indexing and other optimizations.