Oracle query to sort

Question

I have values ​​in my column like [1_A CA 1_H 1_G CB AA AB HG]. If I use order by, then the output am am will be [1_A 1_G 1_H AA AB CA CB HG]. I've tried it so far

select distinct Plant_name from table_plants order by Plant_name

But I need a result like [AA AB CA CB HG 1_A 1_G 1_H].

Answer 1

One way: you can match and put numeric characters in the alphabet, and then sort by column.

SELECT *
FROM table_plants 
ORDER BY CASE 
        WHEN REGEXP_LIKE(Plant_name , '[0-9]')
            THEN 2
        ELSE 1
        END
    ,Plant_name;

Demo

If you want to consider numeric characters starting with a number, you can do this '^[0-9]'.

Also, if you want to use non-regexp functions like this translate, it should do the trick for cases like yours if the underscore is present in all numeric columns.

ORDER BY 
TRANSLATE(Plant_name,'x0123456789','x')

Answer 2

SIGN ASCII REGEXP_REPLACE, :

select distinct Plant_name 
  from table_plants 
 order by sign(ascii(Plant_name)) desc, regexp_replace(Plant_name,'^\d*');

PLANT_NAME
----------
AA
AB
CA
CB
HG
1_A
1_G
1_H

- SQL Fiddle

Answer 3

:

select Plant_name from
table_plants
order by regexp_substr(Plant_name, '^[[:alpha:]]*') , 
  regexp_substr(Plant_name, '^\d*'),
  regexp_substr(regexp_substr(Plant_name, '_[[:alpha:]]*') ,'[[:alpha:]]+') ;