Compare Oracle Table with legacy data that has spaces

Question

I have a legacy goldengated table- Let us call it LEGACY_TABLE. This has 15 columns and 5K rows. This LEGACY_TABLE has column values with a lot of trailing spaces.

My new table, let us call it NEW_TABLE. I created it manually by exporting data and creating sqls. removing spaces. Some Oracle HIGH dates.

Now I need to compare them to see if both tables are exactly the same BUT ignoring spaces. What is the best way to compare the two tables? want to make sure they are exactly the same if I ignore the spaces.

Can this be done by SQL? Excel?

Answer 1

It sounds like you just want

select trim(column1), trim(column2), ... trim(columnN)
  from legacy_table
minus
select column1, column2, ..., columnN
  from new_table

to show what is in the legacy table that is not in the new table and

select column1, column2, ..., columnN
  from new_table
minus
select trim(column1), trim(column2), ... trim(columnN)
  from legacy_table

to show the reverse. You could do a single query with a union all to combine these two queries if you wanted to.