How do I display a field's hidden characters in the result of a query in Oracle?

Question

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

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.

Answer 1

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