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;

I'm building the sql in java through IntelliJ. The main focus is the question I posed. — Heroice18, Dec 15 '21 at 22:15

user1191247 · Answer 1 · 2021-12-16T20:39:05.733

0

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

edited Dec 16 '21 at 20:39

answered Dec 15 '21 at 23:52

user1191247

10,808
2
22
32

Will this work with alpha characters as well? – Heroice18 Dec 16 '21 at 16:20
The addition of `parameter_type ASC` does some alpha sorting. If it does not work exactly as you want it to you will need to update the sample data and expected output in your question. – user1191247 Dec 16 '21 at 16:35
I tried to run that statement in SQL Developer and it came back saying that there was a syntax error within the first IF statement. Could it be because of the convert or the decimal logic within the loop? – Heroice18 Dec 16 '21 at 16:44
@Heroice18 This Oracle version now works correctly and I have added a link to a db<>fiddle. – user1191247 Dec 16 '21 at 20:40

FanoFN · Answer 2 · 2021-12-17T02:08:44.543

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

Order By LPAD Issue

2 Answers2