I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I basically have to treat
NULLs, empty strings and (pure) whitespace as 'blanks' and count the number of non-blank cells. The table contains a mix of
Q1 In the table
TABLE1, I have a column
COLUMN1 nvarchar(32) with the following data distribution:
Value RowCount N/A 80 -- string 'N/A' NULL 20 -- actual nulls
Why does the last of the following queries return unexpected results?
SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, as expected SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, as expected SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, but I expected 100.
Q2 I have another column
COLUMN2 numeric(18, 0) filled with values with no
NULLs or empty strings (but it could contain either/both). But the second of the queries below fails due to a reason I don't understand.
SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END) FROM TABLE1 -- returns full rowcount (100), as expected. SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 --query FAILS! (Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.)
Q3 What's an all-encompassing expression for my requirement of checking a column for NULLs, emptystrings and pure whitespace regardless of a column's datatype? If my columnname comes from a (cursorized) variable
@column, what should I enclose it in and compare it to? I tried working with
nvarchar and using
RTRIM, but frankly I am a bit lost at this point.
I am using SQL Server 2008. Thank you for reading this and for your assistance.