Order By LPAD Issue

Question

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 in Oracle SQL?

(The SQL statement)

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

Answer 1

MySQL

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

Well, as you may already guess, the answer above was for MySQL and posted before the tag change. Unfortunately, Oracle is not my daily database however in the spirit of not trying to post a wrong answer, I did this:

SELECT val,
       CASE WHEN REGEXP_LIKE(val,'^[0-9]')
                   THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
              WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
                   THEN CAST(val AS DEC(5,2)) 
              ELSE 9000*9000 END AS val_check
       FROM mytesting 
ORDER BY CASE WHEN REGEXP_LIKE(val,'^[0-9]')
                   THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
              WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
                   THEN CAST(val AS DEC(5,2)) 
              ELSE 9000*9000 END,
              val;

With the intention of trying to emulate the same idea as the MySQL suggested solution above. Demo fiddle

Answer 2

MySQL - 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;

Oracle - If you are using Oracle you will need to use CASE as it does not have an IF() function. Also, you cannot use CAST as it is not as forgiving as in MySQL.

SELECT * 
FROM data_table
ORDER BY
  CASE WHEN REGEXP_INSTR(parameter_type, '^[0-9]') > 0 THEN TO_NUMBER(REGEXP_REPLACE(parameter_type, '[^0-9\.]', '')) END ASC, parameter_type ASC

This checks to see if the first character is [0-9] and if so removes everything apart from [0-9.] and converts it to a number. db<>fiddle