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.
July 21, 2011 at 5:35 pm
Have you benchmarked this approach vs. stripping the articles off and putting them in a separate column? For example, PRE would contain “The” and TITLE would contain “Big Lebowski”. You can then sort on TITLE and when it’s time to display, just do
echo $pre . ” ” . $title;
July 26, 2011 at 5:56 pm
I haven’t run a benchmark on this – I would image that your technique would be faster when sorting without the article, but would require rewriting some other queries/functions in an existing site to make everything function the same. Let me know if you get a chance to run some comparisons!
January 21, 2012 at 9:38 pm
I realize this article is a bit old but do you have any idea on how to do this but with WordPress posts? I’m using custom post types for a book database and I want to sort it by title, without leading articles of course. I don’t want to resort to renaming the books from The Songs of the Humpback Whale to Songs of the Humpback Whale, The. Any suggestions?
May 11, 2012 at 8:07 pm
Sorry Edel, not off the top of my head I don’t. Please comment back if you find a good solution, I’m sure you aren’t the first person that needed to do that!
October 4, 2012 at 4:30 am
I also need something similar. Did you find a solution? Thanks in advance…
Below is the code for moving the “a”, “an”, and “the” to the end of the title. This good for displaying the edited title. I need something to edit the titles, order them alphabetically and return them to the query array.
$post_title = preg_replace(‘/^(a|an|the)\s+(.+)/i’, ‘\\2, \\1’, $post_title);