[aklug] Re: MySQL sorting alphanumerics

From: Jeremy Austin <jhaustin@gmail.com>
Date: Fri Apr 02 2010 - 20:40:35 AKDT

>
>
> >
> > On Fri, Apr 2, 2010 at 4:30 PM, James Zuelow
> > <James_Zuelow@ci.juneau.ak.us> wrote:
> >> So I have a small MySQL database, with a table that lists the interface
> des=
> >> cription for switches. The descriptions are a text field, even though
> on m=
> >> any models the descriptions are just a number.
> >>
> >> I can pull these out with a SELECT statement, and get results like so:
> >>
> >> SELECT port_name FROM table WHERE switch_id =3D 1 ORDER BY port_name;
> >>
> >> 1
> >> 10
> >> 11
> >> 12
> >> 2
> >> 3
> >> 4
> >>
> >> You can see that's not sorted numerically.
> >>
> >> I can sort them by doing this:
> >>
> >> SELECT port_name FROM table WHERE switch_id =3D 1 ORDER BY port_name+0;
> >>
> >> 1
> >> 2
> >> 3
> >> 4
> >> 10
> >> 11
> >> 12
> >>
> >> That's all fine and good, but some of my switches have modules in them,
> so =
> >> I get this:
> >>
> >> SELECT port_name FROM table WHERE switch_id =3D 2 ORDER BY port_name+0;
> >>
> >> C9
> >> A10
> >> A12
> >> D3
> >> C8
> >> A3
> >>
> >> When I add the "+0" to the query, all of the alphanumeric entries get
> evalu=
> >> ated as "0" and so no longer sort.
> >>
> >> Does anyone know how to convince MySQL to do 1 2 3 4 10 11 12 *AND* A3
> A10 =
> >> A12 C8 C9 D3?
>
>
There's some 'natural' sorting code here
http://lists.mysql.com/internals/9855

and other places around the web.

If port_name were numeric, it would return in numeric order. Since it's
text, another approach is to pad with zeros to however many places you need.

If ports have both names and numbers, then perhaps two separate fields are
called for?

jermudgeon

---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Fri Apr 2 20:41:05 2010

This archive was generated by hypermail 2.1.8 : Fri Apr 02 2010 - 20:41:05 AKDT