如何在Oracle查询中显示字段的隐藏字符?

本文介绍了如何在Oracle查询中显示字段的隐藏字符?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两行具有varchar列，这些列根据Java .equals()的不同而有所不同.我无法轻松地更改或调试针对该特定数据库运行的Java代码，但是我确实有权使用SQLDeveloper直接针对该数据库进行查询.这些字段对我来说看起来是一样的(它们是街道地址，两行之间用一些新行或运输工具行/新行组合隔开).

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

有没有一种方法可以查看所有隐藏字符作为查询的结果?我想避免必须在每行上使用ascii()函数和substr()来找出哪个隐藏字符不一样.

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.

我还将接受一些查询，该查询向我显示哪个字符是两个字段之间的第一个区别.

I'd also accept some query that shows me which character is the first difference between the two fields.

推荐答案

尝试

select dump(column_name) from table

更多信息，请参见文档.

关于查找字符不同的位置，这可能会给您一个想法:

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);