php for creating library sort titles

Question

I use the following in mysql to get titles in libray sort, e.g. The Godfather is sorted as Godfather.

SELECT apnumber, aptitle, IF( aptitle LIKE 'The %', SUBSTRING( aptitle, 5 ) , IF( aptitle LIKE 'A %', SUBSTRING( aptitle, 3 ) , IF( aptitle LIKE 'An %', SUBSTRING( aptitle, 4 ) , aptitle ) ) ) AS sorttitle etc...

What's the most efficient way in PHP to change a string into a library sorted version? That is dropping any "An ", "A " or "The " at the beginning of a title. Am I correct in assuming I need to be looking at something like:

substr_replace( "The " , "" , 0)

Answer 1

I think you are generally correct, yes. As far as I'm aware, there is nothing built in to either PHP or MySQL to assist with this type of sorting. Code implementations I've seen have all used a set of substring replacement rules to get rid of unwanted prefixes, just as you are assuming above. Generally preg_replace() is used so that you can specify a match at the beginning of the string only:

preg_replace("/^The /",'',$title,1);

Something to consider if you're willing to forego a little disk space and normalization would be to store a second column called something like sort_title, and do the prefix removal before inserting the records. This would allow you to index and ORDER BY on the sort_title field within MySQL, and reduce the complexity of your SELECT statements.

As another aside, this would be a great extension to write for PHP!