Eugene Eugene - 6 months ago 15
SQL Question

Finding duplicates in a row

I have a table named TEST. Some fields have NULL value

Country City Street House
US NULL Avenue 14
UK London NULL 15
NULL NULL NULL NULL
NULL NULL Central 16


I need to display items with only one NULL.

So the output should be:

Country City Street House
US NULL Avenue 14
UK London NULL 15


I have stupid solution just compare all possible pairs of columns and check NULL.

Just like this:

SELECT * FROM TEST
WHERE NOT (
(country='NULL' AND city='NULL')
OR (country='NULL' AND street='NULL')
OR (coutry='NULL' AND house='NULL')
OR (city='NULL' AND street='NULL')
OR (city='NULL' AND house='NULL')
OR (street='NULL' AND house='NULL')
)


It works, but understand that it is very inefficient.

Can you recommend more elegant solution ?

Answer
select * from TEST
WHERE  
  CASE WHEN country = 'NULL' THEN 1 ELSE 0 END
+ CASE WHEN city= 'NULL' THEN 1 ELSE 0 END
+ CASE WHEN Street = 'NULL' THEN 1 ELSE 0 END
+ CASE WHEN House = 'NULL' THEN 1 ELSE 0 END = 1