sorting numbers as spelled

--_000_405F5CB98828FA4E9720B6EC52211CEC0C132E5971IOWAEVS09io wa_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Say I have the following data:

+----------------------------------------------------------- ---------------=
-----+
| title =
|
+----------------------------------------------------------- ---------------=
-----+
| ...And justice for all =
|
| 12 Angry Men =
|
| The Abe Lincoln of 9th Avenue =
|
| A Cry for Freedom =
|
| Alice in Wonderland =
|
+----------------------------------------------------------- ---------------=
-----+


And I want to sort it alphabetically, but with the numbers spelled out and =
the leading articles removed. I know I can use trim to get rid of the lead=
ing A/An/The/... but I want to sort 12 in the Ts for twelve, like this:

+----------------------------------------------------------- ---------------=
-----+
| title =
|
+----------------------------------------------------------- ---------------=
-----+
| The Abe Lincoln of 9th Avenue =
|
| Alice in Wonderland =
|
| ...And justice for all =
|
| A Cry for Freedom =
|
| 12 Angry Men =
|
+----------------------------------------------------------- ---------------=
-----+

So it is alphabetized by "Abe", "Alice", "And", "Cry", and "12" as twelve.

Any ideas, or should I just tell the user to spell out numbers?

Thanks!

--_000_405F5CB98828FA4E9720B6EC52211CEC0C132E5971IOWAEVS09io wa_--

Re: sorting numbers as spelled

Robert,
The titles should be sorted as they are stored. The "12" should come
before the other titles, just as they do with other online movie databases.
See http://www.imdb.com/find?s=all&q=angry
The only problem would be of course if someone searches on "Twelve", it
should also return movies with "12" in them. This has more to do with your
full text search engine capabilities.

Mike

At 08:17 AM 7/20/2010, Ramsey, Robert L wrote:
>Say I have the following data:
>
>+---------------------------------------------------------- ---------------------+
>|
>title |
>+---------------------------------------------------------- ---------------------+
>| ...And justice for
>all |
>| 12 Angry
>Men |
>| The Abe Lincoln of 9th
>Avenue |
>| A Cry for
>Freedom |
>| Alice in
>Wonderland |
>+---------------------------------------------------------- ---------------------+
>
>
>And I want to sort it alphabetically, but with the numbers spelled out and
>the leading articles removed. I know I can use trim to get rid of the
>leading A/An/The/... but I want to sort 12 in the Ts for twelve, like this:
>
>+---------------------------------------------------------- ---------------------+
>|
>title |
>+---------------------------------------------------------- ---------------------+
>| The Abe Lincoln of 9th
>Avenue |
>| Alice in
>Wonderland |
>| ...And justice for
>all |
>| A Cry for
>Freedom |
>| 12 Angry
>Men |
>+---------------------------------------------------------- ---------------------+
>
>So it is alphabetized by "Abe", "Alice", "And", "Cry", and "12" as twelve.
>
>Any ideas, or should I just tell the user to spell out numbers?
>
>Thanks!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org