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.

Ask Tom


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
    ;

Need Your Help

Class return value to another function in same class

php class variables methods

Im trying to learn how classes work and I found a problem. How do i pass a variable from a function to another function in same class? I tried using return but it didn't work.

Is there a model.ICharField() in Django, for a case insensitive CharField?

django django-models

There doesn't seem to exist a case independent model.CharField() in Django? That makes case independent unique values and list look-ups somewhat difficult.