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.