SELECT query to deal with "A " and "The " prefixes

Say I am using a SELECT query that returns a list of Titles. I want to alter the query so that it will reformat each Title string that starts with "A " or "The " so that this is moved to the end of the string. To give an example:

BEFORE
"The Three Little Pigs"
"A Tale of Two Cities"


AFTER
"Three Little Pigs, The"
"Tale of Two Cities, A"


I want this so that the ORDER BY will be more logical and thus makes it easier to find the Title in a dropdown control.

How do I alter my SELECT query to achieve this outcome?

Try this"

SELECT Table1.title, IIf(Left([title],3)="The",Mid([title],5,Len([title])-4) & ", The",IIf(Left([title],1)="A",Right([title],Len([title])-2) & ", A",[title])) AS Expr1
FROM Table1;

I think I solved it a few minutes ago. Query below (I am using MS SQL Server hence the use of CHARINDEX)

PHP Code: 

SELECT CASE UPPER(LEFT(Title, CHARINDEX(' ',Title)))
WHEN 'THE' THEN 
    RIGHT(Title, LEN(Title) - CHARINDEX(' ',Title)+1) + ', The'
WHEN 'A'THEN 
    RIGHT(Title, LEN(Title) - CHARINDEX(' ',Title)+1) + ', A'
ELSE
    Title
END
FROM Table1 
ORDER BY Title 


Originally Posted by alansidman

Try this"

SELECT Table1.title, IIf(Left([title],3)="The",Mid([title],5,Len([title])-4) & ", The",IIf(Left([title],1)="A",Right([title],Len([title])-2) & ", A",[title])) AS Expr1
FROM Table1;

Thank you very much Alan. +1

(My version works in MS SQL Server but not when I query an Excel workbook. I need your version for that!)

I needed to make a slight change to your code to check for a space after "A" or "The". Detecting "The" instead of "The " causes "There" to be converted!