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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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 =
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
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 = and cast(origin.view_description as varchar2(4000)) <> destination.description ; – Raj Jun 12 '14 at 20:03

Your Answer


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

Browse other questions tagged or ask your own question.