Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to synchronize a number of tables with string columns between two databases:

  • the source Oracle 10.2.0.3 NLS_CHARACTERSET WE8MSWIN1252, NLS_LENGTH_SEMANTICS BYTE
  • the destination Oracle 10.2.0.5.0 NLS_CHARACTER_SET AL32UTF8, NLS_LENGTH_SEMANTICS BYTE

The source database is an off the shelf product that I have limited ability to change. It stores strings as NCLOB which are truncated with DBMS_LOB.SUBSTR and presented in a view as NVARCHAR2(2000). The destination database stores the same strings as VARCHAR2(4000).

When I execute a statement like

Select origin.view_description, destination.description
from origin, destination
where origin.pk = destination.pk
and origin.view_description <> destination.description

I get hundreds of results that are identical for case, white space and hidden characters. I verified the identical nature using two text tools. The only difference I can find is the length of the description due to the difference in character set.

I tried using COMPOSE, CONVERT and CAST to only detect real changes in the description but this gives the same results as using only an equivalence operator.

How can I compare two strings in SQL that use different character sets and find the ones that have different text?

Edit: Raj asks if I have used CAST as in

where CAST(origin.view_description AS VARCHAR2(4000)) <> destination.description

This yields the same subset as using COMPOSE or CONVERT with AL32UTF8. Two strings that are identical as far as upper/lower case, white space, invisible characters but have a different length. Security restrictions mean I cannot post real data.

share|improve this question
1  
You don't show us real results, so just "doesn't work" probably is very subjective. Try Select cast(origin.view_description as varchar2(4000)) orig, destination.description from origin, destination where origin.pk = destination.pk and cast(origin.view_description as varchar2(4000)) <> destination.description ; –  Raj Jun 12 at 20:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.