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

Hi Kenneth,

Indeed my default NLS_SORT is Dutch. modifying it as you suggest helps me there.

Thanks for your repy.

Roel

Kenneth Koenraadt wrote:
> 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
Received on Tue Jan 18 2005 - 12:34:02 CST