Order by "The"

[PHP Monkeh]

I don't usually post new topics but this one has me stumped   Although the subject isn't that descriptive I couldn't think of what to call it.

 

I'm creating a catalogue, and as such it requires to have alphabetical ordering.  Now I can select all titles beginning with A, B, C etc. and even the ones that start as "The A", "The B" and so on.  The problem is the ordering, all of the titles beginning with "The A" go to the end because of the T.  Is there any way to order by the A value rather than the The.  At the moment it's like this:

 

Almighty DVD Title
Another DVD Title
The Almost Perfect DVD Title

 

Whereas I want it to be like this

 

Almighty DVD Title
The Almost Perfect DVD Title
Another DVD Title

 

Hopefully you can see what I'm trying to achieve.  I assume it's something to do with SUBSTR() or maybe there's some REGEX I'm not aware of, but hopefully someone out theres done this   Cheers.

[PHP Monkeh]

Never mind I fixed it, I just removed the ORDER BY from my MySQL statement and it fixed it.  MySQL is obviously designed perfectly and it was me who was flawed

[aschk]

You removed the ORDER BY clause and it worked ... 

Let me see your SQL statement and DDL, i'm curious to know how.

[PHP Monkeh]

OK I've just noticed why it worked once I took the ORDER BY out.  The system I used to generate the input (it's from a report) was automatically putting them in to the correct order, so my MySQL statement was ordering them by ID, which would be correct in this case (as they were in the right order from the report).

 

So I guess the question still stands, how can you order this way?

[PHP Monkeh]

OK I've figured out how to order by the second word, I used REPLACE() in my SQL statement.  This is my current SQL statement for anyone interested:

 

$query = sprintf("SELECT id, title, REPLACE(title, 'The ', '') AS Title FROM `titles` ORDER BY Title ASC  LIMIT %s, 10", mysql_real_escape_string($limit, $db->dbh));