Oracle-将字符串与utl_raw.cast_to_varchar2函数的结果连接 [英] Oracle - concatenating string with result of utl_raw.cast_to_varchar2 function
问题描述
我试图将字符串连接到utl_raw.cast_to_varchar2函数的结果(也是字符串).它应该是透明的，但我无法在utl_raw.cast_to_varchar2的结果后附加任何内容.
I am trying to concatenate a string to a result of utl_raw.cast_to_varchar2 function (which is also a string). It is supposed to be transparent, but I wasn't able to append anything to a result of 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"
I expect the result to be "new york, ny" test but I only get "new york, ny"
推荐答案
这是可能被视为错误的组合-但是对于Oracle内部如何使用nlssort以及您的客户端来说，这实际上可能是至关重要的处理字符串.例如，在SQL Developer中，当作为语句或脚本时，这似乎可以按预期工作，但是我无法从工作表或查询结果网格中复制并粘贴结果.
This is a combination of what may be considered a bug - but which might actually be vital to how nlssort is used internally by Oracle - and how your client handles strings. In SQL Developer for instance, this appears to work as expected when as a statement or as a script, but I can't copy and paste the results from the worksheet or the query result grid.
使用dump()函数，您可以看到组成结果的字符:
Using the dump() function you can see the characters that make up the result:
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之间.或更清楚一点，没有串联:
I've marked a ^ marker to highlight a 0 that appears in that output, between the cast nlssort() result and the test you're adding. Or a bit more clearly without the concatenation:
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
nlssort()调用将一个空字节(在转储输出中显示为0)添加到结果中.您的客户将其视为字符串的结尾，因此，即使Oracle实际上在串联字符串，您也看不到结果.
The nlssort() call is adding a null byte, shown as 0 in the dump output, to the result. You client is seeing that as the end of the string, so even though Oracle is actually concatenating the strings, you just can't see the results.
您可以删除空值，例如在连接前使用rtrim()进行操作:
You can remove the null, e.g. with rtrim(), before concatenating:
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
或使用较短的原始字符串:
or with the shorter original string:
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
现在可以看到的
没有结尾的空字符.
which you can see now has no trailing null character.
这篇关于Oracle-将字符串与utl_raw.cast_to_varchar2函数的结果连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
