[aklug] Re: MySQL sorting alphanumerics

>> Does anyone know how to convince MySQL to do 1 2 3 4 10 11 12 *AND* A3 A1=
> 0 =3D
>> A12 C8 C9 D3?

I need to solve something similar. Here's a dump of the snippets from
my investigation.

The search tokens that open up Google for this are "natural sort" and
"MySQL". Lots of people want this - it looks like a regular bug/feature
report.

It looks like people are solving this either by adding a separate
sorting column (for large data sets), sorting it in memory on the client
side (for small data sets), or padding the digits with zeroes as Shane
suggests.

http://stackoverflow.com/questions/153633/natural-sort-in-mysql

A key issue is whether or not there is a clear delimiter (like a space).
Some folks are doing it by sorting first on length of field, then the
field, but this only works for pretty uniform-looking data.

ORDER BY LENGTH(field) ASC, field ASC

Here's a novel start of a solution (which fails on some corner cases,
but might make a good starting point):

http://forge.mysql.com/tools/tool.php?id=122

SELECT col FROM TABLE
ORDER BY round(RIGHT(col,length(col)-length('string')))

If you want to hit it with a big hammer, the Drupal folks have a 'natsort':

http://drupalcode.org/viewvc/drupal/contributions/modules/natsort/natsort.install.mysql?revision=1.3.2.1&view=markup&pathrev=DRUPAL-5

This thread pretty much rehashes it:

http://forums.mysql.com/read.php?10,34908,35157,quote=1

Royce
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Sat Apr 3 07:13:24 2010