Questions about handling NULLs and empty strings in nvarchar and numeric fields

Question

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 numeric, bit and nvarchar columns.

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 cast to nvarchar and using LTRIM/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.

Answer 1

Q1

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.

Why would you expect 100 rows? You have 20 rows where the column is NULL. Your expression evaluates to:

SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)

Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'.

Q2

The error is due to implicit conversion and the order of the expressions. In the first query, you are comparing to a string first, then to NULL. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. Try this to see why it doesn't work:

SELECT CONVERT(DECIMAL(10,2), '');

Q3

In order to use the same expression on all data types, you must be able to convert them all to the same data type. So let's say I have a table:

CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2));

INSERT #foo SELECT '1', NULL;
INSERT #foo SELECT NULL, 4.5;
INSERT #foo SELECT '', 5.5;

Now compare the results of these four expressions:

SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') <> '';
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = '';

SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') <> '';
SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';

Answer 2

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, but I expected 100.

Since NULL value is not the same as empty string in SQL, then the result you obtained is correct and makes sense in SQL terms.

Think of NULL as "Not Defined Value" and as such it is not same as an empty string (or any non-null value for that mater) which is a defined value. From an English standpoint, null is the same as nothing but this is not the same in SQL.

Now what do you expect this to return?

SELECT * FROM TABLE1 WHERE C1 NOT IN ('N/A')

Given your data above, it returns no rows.

SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 --query FAILS!

Here the query fails because you are trying to compare a numeric value to an empty string. The data types is not the same. You need to compare values of compatible data types to ensure correct result.

I don't fully understand what you mean by Q3, so I will not be able to help with that.

Be very careful with Nulls they are tricky.

Answer 3

The answers already posted are correct, but here's the practical "take-away": For my purposes, "A null entry is the same thing as a blank entry". If that's true in your world as well then do this: For your evaluating statements, get in the habit of using isnull([fieldname],'') for varchars and isnull([fieldname],0) for numeric fields. That way your null fields are always evaluated consistently, and you'll ALMOST never have unexpected results.