Alphabetically ordering records with "The", "A", "An" etc at the beginning of varchar field

Question

I'm looking for both MySQL and PostgreSQL solutions for this kind of problem.

Say I have a number of records with a title field. The titles are book or movie titles, like "The Cat in the Hat" and "Robin Hood". But while the titles must be displayed in their original form, they ought to be sorted in the way that libraries sort them, which is by moving any article, like "The" or "An" to the end of the title.

So "The Cat in the Hat" is sorted as if it were "Cat in the Hat, The".

What's the best way either to design the schema or write the query so that these records are sorted by title in the same way that libraries sort the title? (I also wish I knew the technical term for this type of ordering by title.) Also, what performance considerations should I be aware of and what indexes should I create?

Answer 1

Why don't you just add a "title_prefix" field to the table and move all these "the" and "a" strings there? When you're ordering you would use the "title" field, and when you are presenting the title you could do the concatenation in any way you wish.

Answer 2

Create a custom function that (sortableTitle, perhaps?) that will modify strings starting with your unwanted words. Finish your query statement with order by sortableTitle(title). This will incur an extra CPU cost, though you'll have to benchmark to know how much so.

You could create an extra column (sortTitle) that is populated by a trigger. This will take up some space, but then your server will be able to sort rows by an index.

Excepting the above, you cannot (without modifying the database server code) directly create an index that is in the order you want. As far as I can tell, that applies to both MySQL and PostgreSQL.