AKS AKS - 6 months ago 8
SQL Question

Get count of columns that are null or empty for each row

How to check row-wise all the columns are null or empty?

I need total count for column is null or blank. Please refer below images to help you.

enter image description here

As result I want count =

4
for the row where StudentId=3, because four columns in that row are null.

Thank you.

Answer

This is not probably the best approach, but it works. I created a table variable to test it, you should adopt the logic in your case:

DECLARE @tbl TABLE (f1 VARCHAR(10),f2 VARCHAR(10),f3 VARCHAR(10))
INSERT INTO @tbl values(NULL,NULL,'fdgfdhfgh')

SELECT *,( CASE WHEN f1 IS NULL THEN 1 ELSE 0 END + CASE WHEN f2 IS NULL then       1 ELSE 0 END + CASE WHEN f3 IS NULL THEN 1 ELSE 0 END ) Nulls FROM @tbl
Comments