[aklug] Re: MySQL sorting alphanumerics

Change the 10 according to pad length.

Take the ordinal value of the first char alpha or numeric and use that
as the first 2 digits for a numeric sort. take everything after that
and pad it with at least 10 0's.

 mysql> select a from xyz order by
concat(ord(substring(a,1,1)),lpad(substring(a,2),10,'0'));

so yeh, I guess you can't put the module identifier in another row?

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 d=
es=3D
> cription for switches. =A0The descriptions are a text field, even though =
on m=3D
> 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 =3D3D 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 =3D3D 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, s=
o =3D
> I get this:
>
> SELECT port_name FROM table WHERE switch_id =3D3D 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 eva=
lu=3D
> 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 A1=
0 =3D
> A12 C8 C9 D3?
>
> James Zuelow
> Network Specialist
> City and Borough of Juneau MIS (907)586-0236=3D
> ---------
> To unsubscribe, send email to <aklug-request@aklug.org>
> with 'unsubscribe' in the message body.
>
>
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Fri Apr 2 19:02:08 2010