Oracle - 将字符串与 utl_raw.cast_to_varchar2 函数的结果连接起来

我正在尝试将字符串连接到结果utl_raw.cast_to_varchar2函数（也是一个字符串）。它应该是透明的，但我无法将任何内容附加到结果中utl_raw.cast_to_varchar2.

这是一个例子：

select  utl_raw.cast_to_varchar2((nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"', 'nls_sort=binary_ai'))) || ' test' 
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual)

我期望结果是"new york, ny" test但我只得到"new york, ny"

这是可能被视为错误的组合 - 但实际上可能对于如何实现至关重要nlssort由 Oracle 内部使用 - 以及您的客户端如何处理字符串。例如，在 SQL Developer 中，当作为语句或脚本时，这似乎按预期工作，但我无法从工作表或查询结果网格复制并粘贴结果。

使用dump()函数你可以看到组成结果的字符：

select dump(utl_raw.cast_to_varchar2((nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"',
  'nls_sort=binary_ai'))) || ' test', 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=20 CharacterSet=AL32UTF8: 22,6e,65,77,20,79,6f,72,6b,2c,20,6e,79,22,0,20,74,65,73,74
                                                                              ^

我已经标记了一个^标记以突出显示0出现在该输出中的演员之间nlssort()结果和test你正在添加。或者在没有连接的情况下更清楚一点：

select dump(utl_raw.cast_to_varchar2(nlssort('ABC')), 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=4 CharacterSet=AL32UTF8: 41,42,43,0

或者甚至更好：

select dump(nlssort('ABC'), 1016) as dumped_result from dual;

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=23 Len=4: 41,42,43,0

The nlssort()调用添加一个空字节，如下所示0在转储输出中，到结果。您的客户端将其视为字符串的结尾，因此即使 Oracle 实际上连接了字符串，您也看不到结果。

您可以删除空值，例如和rtrim()，连接之前：

select rtrim(utl_raw.cast_to_varchar2(nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"',
  'nls_sort=binary_ai')), chr(0)) || ' test' as result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

RESULT                                                                                              
----------------------------------------------------------------------------------------------------
"new york, ny" test

或者使用较短的原始字符串：

select dump(rtrim(utl_raw.cast_to_varchar2(nlssort('ABC')), chr(0)), 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=3 CharacterSet=AL32UTF8: 41,42,43

您现在可以看到它没有尾随空字符。