10 g varchar column ordering by numbers before

Question

I have an Oracle 10g DB, and I have a VARCHAR2 column (2000 characters), name it TEST, which can contain the numbers before, for example:

test
1test
3test

When I call "... asc test order" or just "... test order"

I get results ordered as

test
1test
3test

But I would like to arrange the results like this:

1test
3test
test

So, first numbered inserts, is there a way to achieve this?

Answer 1

NLS_SORT ? (select sys_context('USERENV', 'NLS_SORT') from dual). BINARY, , . - , .

, , :

order by nlssort(test,'NLS_SORT=BINARY')

BINARY. select value from v$nls_valid_values where parameter = 'SORT'. , NLS_SORT.

nlssort() .

Answer 2

substr regexp

order by case when regexp_instr(test,'[0-9]+') = 1 then 
   to_number(regexp_substr(test,'[0-9]+')) 
else 
   null 
end nulls last, test

- - , .

Answer 3

One of the methods:

order by case when substr(test,1,1) between '0' and '9' then 1 else 2 end,
         test

Answer 4

Using substr works fine. If we need numbers in ascending order and numbers in descending order. What will be the workaround.