3

I am running a query in Oracle and that query is ordered by a column that may have values with ampersand. However, it seems that ampersand is being ignored by the sorting algorithm.

For instance:

select * from (
    select '&' txt from dual
    union
    select 'P' txt from dual
    union
    select 'N' txt from dual
)
order by txt

Prints exactly (and correctly, I guess):

&
N
P

However, if I change the text with '&' to '&Z', the result changes:

select * from (
    select '&'||'Z' txt from dual // concatenating just to
                                  // avoid variable substitution
    union
    select 'P' txt from dual
    union
    select 'N' txt from dual
)
order by txt

Result is then:

N
P
&Z

If I change 'Z' to 'A' then the result is:

&A
N
P

It seems that the '&' is not being considered by ORDER BY clause. Does anyone know if this is expected behavior or if I am missing some configuration step? I understand that one needs to escape ampersand for inserts or updates. But thing is that the character is already in the table!

Thanks in advance, guys.

kaya3
  • 47,440
  • 4
  • 68
  • 97
Nei Barbosa
  • 53
  • 6

2 Answers2

4

It is the effect of linguistic sorting:

SQL> alter session set nls_sort=binary;

Session altered.

SQL> get afiedt.buf
  1  select * from (
  2  select '&' txt from dual
  3  union
  4  select '&'||'Z' txt from dual
  5  union
  6  select '&'||'A' txt from dual
  7  union
  8  select 'P' txt from dual
  9  union
 10  select 'N' txt from dual
 11  )
 12* order by txt
SQL> /

TX
--
&
&A
&Z
N
P

SQL> alter session set nls_sort = 'Dutch';

Session altered.

SQL> get afiedt.buf
  1  select * from (
  2  select '&' txt from dual
  3  union
  4  select '&'||'Z' txt from dual
  5  union
  6  select '&'||'A' txt from dual
  7  union
  8  select 'P' txt from dual
  9  union
 10  select 'N' txt from dual
 11  )
 12* order by txt
SQL> /

TX
--
&
&A
N
P
&Z

It does make a difference for what language you are sorting. Some characters have different values depending on their language. With binary you sort using the ascii values.

  • Linguistic sorting has [ignorable characters](http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm#i1006286) but no idea if that can include `&` as punctuation. – Alex Poole Apr 16 '11 at 09:01
  • Nice reading about the ampersand Not that it makes anything much clearer but it is a complex thing. –  Apr 17 '11 at 20:55
  • Also see where it is listed as a punctuation mark. –  Apr 17 '11 at 21:01
  • Thanks @ik_zelf, it worked like you said. @alex-poole I'll take a look at the ignorable characters page you posted. – Nei Barbosa Apr 18 '11 at 18:20
1

It may depend on your NLS_LANGUAGE and NLS_SORT setting.

I get the following on Oracle 10g, with NLS_LANGUAGE = AMERICAN and NLS_SORT = (null).

TXT 
--- 
&   
N   
P   

TXT 
--- 
&Z  
N   
P
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158