sql - Comparing all data in two tables whilst ignoring certain differences -

i trying compare 2 sql server database tables. i've found various methods online, seem work, other don't.

the 1 used worked was:

select * table1 except select * table2

the issue (as far aware) is, table says there difference between 'null' value , '0'. correct, there difference.

however question is, there way comparison difference check, whilst ignoring conditions such null , 0.

you can use

select isnull(column1, 0) column1 table1  except  select isnull(column1, 0) column1 table2 

to consider values 0 , null same.

also, if wish consider more values same null = 0 = '' (empty string)

you can use case:

select case when (column1 null or column1 = '') 0 end column1 table1 except  select case when (column1 null or column1 = '') 0 end column1 table2