Alphabetical Order - Eliminating the "THE"

Hi,
This may seem like a simple problem, and I'm somewhat embarrassed that
I've been developing for 7 years and haven't been asked to deal with
this - but when you are ordering a list alphabetically, HOW do you
factor out the preceeding "The" in your list items when you do your
ordering. For example in a list of movies such as:
Into the Blue
Madagascar
Spaceballs
The 40-year old virgin
The Exorcism of Emily Rose
doing a standard Order By on the title field would render the list like
that, but according to the rules of english this is an incorrect
ordering, since The 40-year old virgin and The King and I are both
ordered at the end and should instead be ordered at the top. How do
you get this to happen? Here is some generic sample code that would
order the list the way it appears above. How can I modify the code to
order it according to the rules of English.
SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
Thanks in advance.You will need to order on an expression. For example, try the following:
select
*
from
movies
order by
case when left(title,4) = 'The ' then substring(title,5,255) else Title
end
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>|||I don't really think that it's an English rule for ordering, but I could be
wrong.
For "special" ordering like this, I would add an "order_title" column.
The titles would look like this in the column:
Into the Blue
Madagascar
Spaceballs
40-year old virgin (or Forty-year old virgin)
Exorcism of Emily Rose
Establish the rules and when inserting, format the title and insert the
formatted title in that column .
I'm sure that you'll find other exceptions like titles that start with "A
...".
So your query becomes:
SELECT movie_title, producer, cost from tblMovie ORDER BY order_title
You could, like JT has suggested, incorporate this into the ORDER BY clause
or write a function to do this.
But, depending on the size of this table and other rules may may be added,
this could really slow down your query.
It may be better to take the time when inserted to get it right.
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>