omitting "the" in alphabetizing

[thinsoapy]

My MySQL database contains books and movies, many of which start with "The". I don't care how the text value is stored, but I need to be able to have records sort by the word following "the".

Is this possible, or do I have to create some kind of string checker to move "The" to the end of my records before storage in the db - and then another to put it back when records are called and displayed?

ie: I want

The Affair (rather than Affair, The)
Godzilla
North by Northwest
The Shawshank Redemption

and not:

Godzilla
North by Northwest
The Affair
The Shawshank Redemption

Thanks,

Dave

[brandonschnell]

if you want to use the database for sorting you're going to need to create a new column for listings (drop "A" and "The" from the start of titles). otherwise use php to sort (not as efficient). in another database (not mysql) you could use a stored procedure or custom function database side to do the sorting.

[[deleted]]

Even in mysql you can use a CASE to sort on the name itself or on a substring of the name if the name begins with 'The'.

However, this is VERY inefficient.

[sarahk]

A bonus on this approach is that the processing effort (dropping "the") happens (once) when the movie is added not whenever the page is rendered which just helps to speed things up a bit.