Can anyone lend me a hand as to what I should append to my ORDER BY statement to sort these values naturally:
1 10 2 22 20405-109 20405-101 20404-100 X Z D
Ideally I'd like something along the lines of:
1 2 10 22 20404-100 20405-101 20405-109 D X Z
I'm currently using:
ORDER BY t.property, l.unit_number
where the values are
I've tried doing
l.unit_number * 1 and
l.unit_number + 0 but they haven't worked.
Should I be doing sort of
ORDER conditional, such as
Case When IsNumeric(l.unit_number)?
This will do it:
SELECT value FROM Table1 ORDER BY value REGEXP '^[A-Za-z]+$' ,CAST(value as SIGNED INTEGER) ,CAST(REPLACE(value,'-','')AS SIGNED INTEGER) ,value
The 4 levels of
the ORDER BY:
REGEXPassigns any alpha line a 1 and non-alphas a 0
SIGNED INTSorts all of the numbers by the portion preceding the dash.
SIGNED INTafter removing the dash sorts any of the items with the same value before the dash by the portion after the dash. Potentially could replace number 2, but wouldn't want to treat 90-1 the same as 9-01 should the case arise.
Demo: SQL Fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!Donate Us With