[aklug] MySQL sorting alphanumerics

From: James Zuelow <James_Zuelow@ci.juneau.ak.us>
Date: Fri Apr 02 2010 - 16:30:06 AKDT

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?

James Zuelow
Network Specialist
City and Borough of Juneau MIS (907)586-0236=
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Fri Apr 2 16:30:15 2010

This archive was generated by hypermail 2.1.8 : Fri Apr 02 2010 - 16:30:15 AKDT