[aklug] Re: MySQL sorting alphanumerics

The ideal solution to your problem is, as you seem to have settled on,
to separate out your data, and order by each element you're interested
in. However, it is possible to reliably sort on your existing data.

Shane seems to have gotten the closest, though in his latest the numeric
portions aren't sorting properly. Provided your data is always one
optional letter, followed by numeric data, then you can do this with
MySQL with relatively little fuss. If you get much more complicated
you're going to run into MySQL's lackluster text processing ability.

So, given a table:

    mysql> select * from t;
    +-----+
    | k |
    +-----+
    | 1 |
    | 100 |
    | 10 |
    | A10 |
    | 200 |
    | 11 |
    | B2 |
    | B20 |
    | B9 |
    | C6 |
    +-----+

You would need to separate out the letter, then sort on the numeric.

    mysql> select * from t order by k+0, substring(k, 1, 1), substring(k, 2) + 0;
    +-----+
    | k |
    +-----+
    | A10 |
    | B2 |
    | B9 |
    | B20 |
    | C6 |
    | 1 |
    | 10 |
    | 11 |
    | 100 |
    | 200 |
    +-----+

In other words, attempt to compare the values numerically; if they are
equivalent, compare the first character; if those are equivalent,
compare everything after the first character numerically.

They should probably be casts, instead of +0, but MySQL doesn't seem to
care.

Incidentally, PostgreSQL provides a bit more flexible string handling.
Given the table:

> select * from t;
        k
    ---------
     1
     10
     200
     11
     B2
     A10
     B20
     B9
     C6
     100
     test 10
     Bar 12

Sorted:

> select * from t order by substring(k from '^([[:alpha:]]+)'), substring(k from '([[:digit:]]+)$')::int;
        k
    ---------
     A10
     B2
     B9
     B20
     Bar 12
     C6
     test 10
     1
     10
     11
     100
     200

Essentially, it extracts the leading string portion, compares; if
they're equivalent, it extracts the trailing numeric portion and
compares.

--
Michael Fowler
www.shoebox.net
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.