[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

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
0
DerekWatling
Asked:
DerekWatling
  • 3
2 Solutions
 
ftsaoCommented:
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
 
NovaDenizenCommented:
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
 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now