Re: How to handle a "the"

In article <3962B78B.B975F954@friedspamdigitalhit.com>, ianevans@friedspamdigitalhit.com (Digital Hit Entertainment) wrote: > Getting ready to do a table of film awards and I'm wondering what is the > best way to handle the sorting of "The": is it possible/more efficient > in the SQL select statement or in the PHP scripting? > > Is there a way to do that in the SQL select statement? Should I enter it > as "Green Mile, The" and have PHP reverse it somehow? You would use the "replace" command to do that in the 'select' statement. select title, award, replace(title, "The ","") as sortedtitle from AwardList order by sortedtitle Note, of course, that your calculation will need to be more complex: it will need to handle 'A' and 'An' as well, and will probably need a way to distinguish the beginning of a line if you use capital letters inside the title string. Maybe something like: select title, award, replace(replace(concat("XXX_", title), "XXX_The ",""),"XXX_","") as sortedtitle from AwardList order by sortedtitle Assuming you don't have any occurences of "XXX_" in your titles. In a database with 'calculation' fields, I do this with a pre-defined calculation. Depending on how many records you have, you might want to use PHP to populate a fake calculation field instead. Another option would be to *never* put a starting article in your 'title' field. Have a separate column for starting articles, and when you need it, concat the 'startart' field with the 'title' field. This gives you the option of easily having more than one format in your displays: Green Mile The Green Mile Green Mile, The (The) Green Mile would then be trivial to create. Jerry