Vinu Vinu - 25 days ago 5
SQL Question

How to write a WHERE to equate on two columns contains both NULL and empty values

Table structure and values

Given is my table structure, now I need to write a query to compare "STRVAL1 and STRVAL2" then "BVAL1 and BVAL2", if both are not same, then that row should be returned.

Expected result...

enter image description here

Please use the bellow script for data

DECLARE @TAB TABLE (ID INT, STRVAL1 VARCHAR(8), STRVAL2 VARCHAR(8), BVAL1 BIT, BVAL2 BIT)

INSERT INTO @TAB VALUES
(1, NULL, NULL, NULL, NULL)
, (2, '', NULL, NULL, NULL)
, (3, '', '', NULL, NULL)
, (4, 'VAL', 'VAL', NULL, NULL)
, (5, 'VAL1', 'VAL2', NULL, NULL)
, (6, NULL, NULL, 0, NULL)
, (7, NULL, NULL, 0, 0)
, (8, NULL, NULL, 1, 1)
, (9, NULL, NULL, 1, 0)

SELECT ID, STRVAL1, STRVAL2, BVAL1, BVAL2
FROM @TAB

Answer

You can just use comparisons, although NULLs make this a bit cumbersome:

select t.*
from @tab t
where ((strval1 <> strval2) or
       (strval1 is null and strval2 is not null) or
       (strval1 is not null and strval2 is null)
      ) or
      ((bval1 <> bval2) or
       (bval1 is null and bval2 is not null) or
       (bval1 is not null and bval2 is null)
      );