oracle sorting with text as number

Question

My NLS settings:

NLS_SORT    POLISH
NLS_COMP    BINARY

Simple test query:

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;

Actual result:

x
-----
11117
12988
14659
1532
18017

Desired result:

x
-----
1532
11117
12988
14659
18017

Question:

Is there a NLS setting that will help me achive desired result? I know I can do order by to_number(x) or, even better, order by lpad(x, 5), but it's not good in this case - I need a system-wide solution that won't require query change.

What I tried:

• order by nlssort(x, 'nls_sort=binary');
• alter session set nls_sort='binary';

Answer 1

Maybe a somewhat odd solution, but ...

1. Rename your TABLE_SOMETHING to TABLE_OTHER.
2. Create a view TABLE_SOMETHING on top of the TABLE_OTHER which will TO_NUMBER() the troubled string column of yours to the same column name.
3. If the table is, application-wide, being modified, then create INSTEAD OF triggers over the view TABLE_SOMETHING.
4. Recompile possibly invalidated packages.

Won't be particularly performant when it comes to modifying large quantities of records, won't allow for truncating the table, but it might solve the ordering problem.