[aklug] Re: MySQL sorting alphanumerics

Nevermind.. needs tweaking to work right.

Shane Spencer wrote:
> 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 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.
>>
>>

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