10g ordering varchar columns when containing numbers in front

Question

I have an Oracle 10g DB and have a VARCHAR2 (2000 Character) column lets name it TEST which can contain numbers in front for example:

test
1test
3test

When I call "... order by TEST asc" or simply "... order by TEST"

I get the results ordered like

test
1test
3test

But I would like to get the results ordered like this:

1test
3test
test

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

Answer 1

What is your NLS_SORT set to? (select sys_context('USERENV', 'NLS_SORT') from dual). If it is BINARY then the sort order is based on the numeric value of each character, so it's dependant on the database character set. If it's something else then you might want to override it.

You can change the sort order at database or session level by modifying that parameter, but you can also change it for a single query:

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

Depending on your character set you might need to experiment with different values instead of BINARY. You can get a list of all the valid values with select value from v$nls_valid_values where parameter = 'SORT'. But note the potential performance impacted mentioned in the NLS_SORT documentation.

The nlssort() function is documented here.