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.
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.