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
DerekWatlingAsked:
Who is Participating?
 
NovaDenizenConnect With a Mentor Commented:
Just taking the string type and casting it to a number will discard everything after the first part of a string that looks like a number.

SELECT '3-7' + 0;
will return '3'.  So to sort numerically in that style you just have to do the same thing in the ORDER BY clause.

SELECT col FROM mytable ORDER BY (col+0);
0
 
ftsaoConnect With a Mentor Commented:
You could try the following:

SELECT id, my_string, IF (ABS(my_string) > 0, LPAD(ABS(my_string), 99, '0'), my_string) AS custom_order
FROM tbl
ORDER BY custom_order

Just make sure the length in LPAD (str, len, padstr) is longer than the length of your string so it sorts properly.
0
 
DerekWatlingAuthor Commented:
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
0
 
NovaDenizenCommented:
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')
0
 
NovaDenizenCommented:
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')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.