0

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?

Marco
  • 83
  • 1
  • 10
  • Hi , can you load both the tables in one DB and do a MINUS , like SELECT * FROM LEGACY_TABLE MINUS SELECT * FROM NEW_LEGACY_TABLE and the other way around and see if you get any rows. – Himanshu Kandpal Apr 03 '23 at 14:50
  • Both are in one Database. How do I deal with the spaces? – Marco Apr 03 '23 at 14:53
  • 2
    What does "Some Oracle HIGH dates" mean? – Justin Cave Apr 03 '23 at 15:03
  • 1
    Are you *only* concerned about trailing spaces? You can use `rtrim()` if so, on all of the problem columns, in one or both tables. It's unclear if you mean *all* spaces. Knowing what "creating sqls. removing spaces" actually involved might be useful; as would knowing that "Some Oracle HIGH dates" means. – Alex Poole Apr 03 '23 at 15:05
  • Yes, if spaces are ignored, data should be exactly same in both tables. Thats what I am trying to make sure. I created the NEW_TABLE by exporting LEGACY_TABLE data and manually removing spaces and creating insert queries and executing them. – Marco Apr 03 '23 at 15:41

1 Answers1

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384