如何在 Oracle 中的查询结果中显示字段的隐藏字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5092703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I display a field's hidden characters in the result of a query in Oracle?
提问by Chris Williams
I have two rows that have a varchar column that are different according to a Java .equals(). I can't easily change or debug the Java code that's running against this particular database but I do have access to do queries directly against the database using SQLDeveloper. The fields look the same to me (they are street addresses with two lines separated by some new line or carriage feed/new line combo).
我有两行有一个 varchar 列，根据 Java 不同.equals()。我无法轻松更改或调试针对此特定数据库运行的 Java 代码，但我确实可以使用 SQLDeveloper 直接对数据库进行查询。这些字段对我来说看起来相同（它们是街道地址，两行由一些新行或回车/新行组合分隔）。
Is there a way to see all of the hidden characters as the result of a query?I'd like to avoid having to use the ascii()function with substr()on each of the rows to figure out which hidden character is different.
有没有办法查看作为查询结果的所有隐藏字符？我想避免必须在每一行上使用该ascii()函数substr()来确定哪个隐藏字符不同。
I'd also accept some query that shows me which character is the first difference between the two fields.
我也会接受一些查询，显示哪个字符是两个字段之间的第一个差异。
回答by René Nyffenegger
Try
尝试
select dump(column_name) from table
More information is in the documentation.
更多信息在文档中。
As for finding the position where the character differs, this might give you an idea:
至于找到角色不同的位置，这可能会给你一个想法：
create table tq84_compare (
id number,
col varchar2(20)
);
insert into tq84_compare values (1, 'hello world');
insert into tq84_compare values (2, 'hello' || chr(9) || 'world');
with c as (
select
(select col from tq84_compare where id = 1) col1,
(select col from tq84_compare where id = 2) col2
from
dual
),
l as (
select
level l from dual
start with 1=1
connect by level < (select length(c.col1) from c)
)
select
max(l.l) + 1position
from c,l
where substr(c.col1,1,l.l) = substr(c.col2,1,l.l);
回答by álvaro González
SELECT DUMP('á?', 1016)
FROM DUAL
... will print something like:
...将打印如下内容：
Typ=96 Len=3 CharacterSet=WE8MSWIN1252: 80,c1,d1
