MYSQL Query – Sort Alphabetical Exclude Articles (‘a’, ‘an’, ‘the’)

I just ran across this query I had to construct a while ago which can be useful when trying to sort alphabetically, but some of the data contained the articles ‘a’, ‘an’, or ‘the’ which alphabetized the results incorrectly. This query could be useful in a number of situations – album titles, company names, movie titles, book titles, and more.

For example, ‘The Big Lebowski’ ought to appear in the B’s, but if you simply did this query, it will show up in the T’s.

SELECT `id`, `title` FROM `movies` ORDER BY `title`;

With a little MYSQL magic, we can sort alphabetically by title, ignoring any leading articles, while still preserving the full title.

SELECT `id`, `title` FROM `movies` ORDER BY TRIM(LEADING 'a ' FROM TRIM(LEADING 'an ' FROM TRIM(LEADING 'the ' FROM LOWER(`title`))));

Basically the ORDER portion of the query just trims off any instances of ‘a’, ‘an’ or ‘the’ (case insensitive) to get the desired order, but the column `movies` will still return the full, unaltered title.