Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If you have a variable @column, why don't you add a variable @datatype and have 3 different versions, one for each datatype? –  ypercube Aug 1 '12 at 0:04
    
Instead of trying to create a monstrous condition that will work with all datatypes? –  ypercube Aug 1 '12 at 0:04
    
For Q1, read this answer: Why does NOT IN with a set containing NULL always return FALSE/NULL? –  ypercube Aug 1 '12 at 0:11
1  
Can you elaborate on Q3, how can you have an empty string or "pure whitespace" in a numeric column? It's either NULL or it's a valid number. –  Aaron Bertrand Aug 1 '12 at 0:55
add comment

3 Answers

up vote 4 down vote accepted

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), ''), '') = '';
share|improve this answer
    
Aaron, to answer your question on the parent post, isn't the third INSERT above inserting an empty string '' in a numeric field? –  Anonymous Maximus Aug 1 '12 at 17:40
1  
@AnonymousMaximus no, a (the first column) is varchar, b (the second column) is numeric. –  Aaron Bertrand Aug 1 '12 at 17:49
    
D'oh... yet another case of post-lunch midday blindness. Also, just to be sure, I tried INSERT #foo SELECT 'M', ''; and it gives me the same 8114 error. Thank you for the education. =) –  Anonymous Maximus Aug 1 '12 at 17:52
add comment

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.

share|improve this answer
1  
If you do that, why do you allow Nulls in the first place? –  ypercube Aug 1 '12 at 17:06
4  
Or why not store NULL always instead of sometimes storing some token value that really means NULL? Using 0 is especially problematic for this - what if 0 is a valid value? Do you really want to have to decide if it's supposed to be 0 or if it's 0 because it was supposed to be NULL? Perhaps reading all the dialog here will be helpful. –  Aaron Bertrand Aug 1 '12 at 17:13
    
Because I didn't write any of the apps that use my db's. I have to work with what I'm handed.... In my world, "null" means, "It hasn't been set" which 99.9 pct of the time is the same as "It's blank". I recognize that that's not true for everyone. –  Bob Aug 1 '12 at 17:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.