Re: Oracle will ignore positions 1000 and after in a string field or expression for sorting using an ORDER BY clause.

Hi Roel,

Please pay attention to the NLS_SORT parameter :

SQL> create table test_order_by(num number(10) not null, val varchar2(2000) not null);

SQL> insert into test_order_by values(4, lpad('X', 999, 'X') || 'D' );
SQL> insert into test_order_by values(5, lpad('X', 999, 'X') || 'E' );
SQL> insert into test_order_by values(6, lpad('X', 999, 'X') || 'F' );

SQL> select num,substr(val,1000,1)
  2 from test_order_by
  3 order by val desc;

       NUM S
---------- -

         4 D
         5 E
         6 F

SQL> alter session set nls_sort=binary;

SQL> select num,substr(val,1000,1)
  2 from test_order_by
  3 order by val desc;

       NUM S
---------- -

         6 F
         5 E
         4 D

• Kenneth Koenraadt