I have alpha numeric data in the column in oracle database and I need to sort it, I tried with writing function which check number in order by case but it didn't work

1 
55
82
u1 
2 
11 
60 
90 
3B1 
3B2 
PORT/0/635
72 
75 
77 
85 
118

order by
(
        CASE 
            WHEN is_numeric(col1) is 1 THEN
                to_number(col1)
            ELSE
                col1
        END
    );

is_numeric is a customized function which check and returns 1 if the value consists of only numbers.

output should be

1
2
11
55
60
72
75
77
82
85
90
118
3B1
3B2
PORT/0/635
u1

Any Help, Thanks

CodePudding user response：

to_number() supports conversion error handling.

select   *
from     t
order by TO_NUMBER(col1 DEFAULT null ON CONVERSION ERROR) nulls last
        ,col1
  

Fiddle