Hi Richard, all!
Richard Gagnon wrote:
> Sorting a varchar field alphabetically with correct numerical order help
> needed
>
>
>
> I have a varchar 50 field that contains product names, which are typically
> numerical, alphabetical and punctuation thrown in. I would like to have them
> returned in some sort of order that is roughly alphabetical, but with the
> numbers in numerical order. The basic Order By clause does not do it
> correctly.
>
> [[...]]
>
>
> In the above, the #2 JCAL should be second, otherwise, list is correct. I
> could also live with the values beginning with # or any alpha character
> coming after the numerical ones, but the 1, 11, 2, order is the issue.
This is a perfect example why you have to differ between string and
numeric data types when declaring your column.
>
> Does anyone have any idea how to do this? I have been playing around with
> various suggested ways, including casting and converting, but so far have
> not been able to solve this. Any ideas would be greatly appreciated.
There is no standard way (in SQL) to do the sorting you want based just
on those strings, so I can think of three choices:
1) Don't let the database do the sorting but do it in the application.
It will still be some effort to do this (code the routine), also it
will require you to read all (qualifying) data from the DB into your app
before it starts sorting and can return the first row, so you get
latency (time for data transfer!).
2) Modify your product names so that string sorting is possible.
This could be done by inserting leading zeros at the front of the
numbers to bring them all to the same fixed length, then ORDER BY will
ensure the sequence 001, 002, 011.
The obvious drawback is that it would modify the product names which get
returned and displayed.
3) Split your product names into a numeric part and a string part.
You could add two columns (one numeric, one string) to your table
which take these parts and then do ORDER BY on these columns, not on the
original name.
You need never return or use these columns externally, they would just
be set on INSERT and maintained on UPDATE.
The tricky part will be to extract these parts from the product name,
but it should be possible to do that with a sensible result.
(It would even allow you to order by Roman numerals if your extract
routine is good enough ...)
IMO, introducing the additional columns is the safest approach:
1) Sorting in the application prevents you from using any standard
application (say, a report generator) and must be duplicated in all
applications using the data.
2) Modifying the data is not the proper thing to do.
3) Maintaining the separate columns might even be automated (triggers on
INSERT and UPDATE), and the ORDER BY can be used in all applications.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team,
joerg.bru...@sun.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
