在Oracle ORDER BY中会忽略“&"字符 [英] Ampersand (&) character is being ignored in Oracle ORDER BY

问题描述

我正在Oracle中运行查询，并且该查询由可能带有&符号的列排序.但是，排序算法似乎忽略了&符.

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.

例如:

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

但是，如果我用'&'更改文字更改为& Z"，结果将更改:

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

结果如下:

N
P
&Z

如果我将"Z"更改为"A"，则结果为:

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

&A
N
P

似乎&"不被ORDER BY子句考虑.有人知道这是否是预期的行为，或者我是否缺少某些配置步骤?我知道有人需要使用&符号进行插入或更新.但是问题是角色已经在桌子上了！

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!

谢谢，伙计们.

推荐答案

这是语言排序的作用:

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

这确实对您要选择的语言有所不同.某些字符根据其语言而具有不同的值.对于二进制文件，您可以使用ascii值进行排序.

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.

这篇关于在Oracle ORDER BY中会忽略“&"字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！