Order By LPAD Issue-CodePudding

Currently I'm trying to take a list of values from my table and order them alphanumerically so they appear from number to letters. For example I have this data set

3
8
56
70
90
AK
CN
PP
PQ
W3
0.5
0.6
0.8
040
070
1.2
1.5
1.6
100
150
187
2.8
250
3.0
6.3
800
8mm

And I want it to print 0.5 first and then W3 last. I am using an Lpad to grab the data, but it displays like shown above, with no ordering. Is there a way I can sort these alphanumerically?

(The SQL statement)

SELECT * 
FROM data_table
ORDER BY LPAD(parameter_type, 10) ASC 
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

CodePudding user response：

Try converting to decimal and checking for value greater than zero -

SELECT * 
FROM data_table
ORDER BY IF(
    CONVERT(parameter_type, DECIMAL(6, 2)) > 0,
    CONVERT(parameter_type, DECIMAL(6, 2)),
    99999
) ASC, parameter_type ASC;

CodePudding user response：

Perhaps like this:

SELECT val,
       val REGEXP '^[a-zA-Z]',
       CAST(val AS DECIMAL(14,4))
FROM mytesting 
ORDER BY CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END ASC,
         CAST(val AS DECIMAL(5,2)) ASC;

First, I use CASE expression REGEXP to check whether the leading value is alphabet or number; if the value starts with alphabet, I assign to 1 if not I'll assign it to 0 so it'll be on the top of an ascending order. Then I add a second order where I change the val datatype to decimal using CAST. I put both operation in SELECT to see what the value it return after the filtering. Since the purpose they're in SELECT is for viewing purpose only, you can remove them from the final query so like this should work:

SELECT val
FROM mytesting 
ORDER BY CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END ASC,
         CAST(val AS DECIMAL(5,2)) ASC;

Alternatively, if you found those values to be useful and you want to use them, you can simplify the query to something like this:

SELECT val,
       CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END AS val_check,
       CAST(val AS DECIMAL(14,4)) AS val_convert
FROM mytesting 
ORDER BY val_check ASC,
         val_convert ASC;

Demo fiddle