SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

oracle sort text as number

maialitharFeb 6 2014 — edited Apr 2 2014

Is there a NLS or other system-wide setting that will help me achieve what I want without changing my query? I know I can do order by to_number(x) or order by lpad(x, 5), but it's not what I want.

Altering session and setting NLS_SORT = 'BINARY' did not help.

Sample query Actual result Desired result My NLS settings

Sample query	Actual result	Desired result	My NLS settings
select * from ( select '11117' as x from dual union select '12988' as x from dual union select '14659' as x from dual union select '1532' as x from dual union select '18017' as x from dual ) order by x;	`x ----- 11117 12988 14659 1532 18017`	`x ----- 1532 11117 12988 14659 18017`	`NLS_SORT POLISH NLS_COMP BINARY`

select * from (

select '11117' as x from dual

union

select '12988' as x from dual

union

select '14659' as x from dual

union

select '1532' as x from dual

union

select '18017' as x from dual

) order by x;

NLS_SORT POLISH
NLS_COMP BINARY

One more thing - I believe it worked before, when I was using Oracle DB 11.1.0.something. It stopeed working after datapump export/import to Oracle DB 11.2.0.4. Are there any differences between these two versions?

I put this topic in General section because I don't want to change my query - I want to know if there are other options.

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 30 2014

Added on Feb 6 2014

4 comments

2,077 views