asked on

Sorting strings numericaly

I am needing to sort a string column numerically. The data looks something like
1, 10, 12, 37, 5, 3-4, cnr

and needs to be sorted as
1, 3-4, 5, 10, 12, 37, cnr

using LPAD with zeros I have got as far as
1, 5, 10, 12, 37, 3-4, cnr

SOLUTION

Frank Tsao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

NovaDenizen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DerekWatling

ASKER

I dont have any negative numbers as such, but "abs(col)" and "col + 0" return the same value on my data. This column is Street Numbers, but comes from a number of (unreliable) sources. After much fiddling I am getting the best results with:

select
StreetNo,
lpad(abs(StreetNo), 25, '0') as sort1,
lpad(abs(StreetNo), 25, '0') like lpad(StreetNo, 25, '0') as sort2
from tbl_fh_owner
order by sort1, StreetNo, sort2

NovaDenizen

This does the same thing without returning the extra columns.

select
StreetNo,
from tbl_fh_owner
order by
lpad(abs(StreetNo), 25, '0'),
StreetNo,
lpad(abs(StreetNo), 25, '0') like lpad(StreetNo, 25, '0')

NovaDenizen

Well, absent the comma anyway.

select
StreetNo
from tbl_fh_owner
order by
lpad(abs(StreetNo), 25, '0'),
StreetNo,
lpad(abs(StreetNo), 25, '0') like lpad(StreetNo, 25, '0')