# oracle PL/SQL: sort rows

I run a query with a order by but my column is not the order a want to be in

I wanted it like this:

##### PRODUCT

PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6 PROD_7 PROD_8 PROD_9 PROD_10

but it gives me this

##### PRODUCT

PROD_1 PROD_10 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6 PROD_7 PROD_8 PROD_9

## Answers

You'll either need to add a 0 for each position such as 01 for numbers 1-99 or 001 for 1-999. OR you'll have to split out the numeric values and sort on two different columns.

You're trying to lexographically sort something which is actually partially numeric. You could prefix zeroes (i.e. PROD_000001) but that's brittle. In real application, I assume that Prod 10 is actually temporally later than Prod 1, so you'd order by creation datetime.

SELECT to_number(substr(colname,INSTR(column_name,'_')+1))) prodno, column_name from table_name order by prodno

Not very elegant solution, but this should work. (As I don;t have access to Oracle, parameters to function might need tweaking.) This first get the position on *_* using which it gets the number using sub-string which is then converted to number. You might also have to look at performance if table size is big

You will need to sort twice (note I changed from regexp_replace to regexp_substr to allow for null return value)

with a as ( select 'PROD_1' product from dual union all select 'PROD_10' product from dual union all select 'PROD_2' product from dual union all select 'PROD_3' product from dual union all select 'PROD_4' product from dual union all select 'PROD_5' product from dual union all select 'PROD_6' product from dual union all select 'PROD_7' product from dual union all select 'PROD_8' product from dual union all select 'PROD_9' product from dual union all select 'DECEAD_1' product from dual union all select 'DECEAD_10' product from dual union all select 'DECEAD_2' product from dual union all select 'DECEAD_20' product from dual union all select 'TREE_FROG' product from dual ) select PRODUCT , regexp_substr(product,'[^[:digit:]]*') --return all non numeric , regexp_substr(product,'[0-9]+') from a order by regexp_substr(product,'[^[:digit:]]*') , TO_NUMBER(regexp_substr(product,'[0-9]+')) --note explicit numeric cast ;