Check numeric column for empty cells

datatypesnull

How can I check for a numeric column for empty values

I want something like this:

select * from table where column_numeric = ''

This does not works obviously, but what can be the equivalent of this for numeric column?

Best Answer

A numeric column without a value is null:

select * 
from the_table
where column_numeric is null;

There is no equivalent concept of an "empty string" for numbers.

Related Solutions

Sql-server – Questions about handling NULLs and empty strings in nvarchar and numeric fields

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'.

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), '');

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), ''), '') = '';

Sql-server – How to check for a null or empty table-valued parameter

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Best Answer

Related Solutions

Sql-server – Questions about handling NULLs and empty strings in nvarchar and numeric fields

Sql-server – How to check for a null or empty table-valued parameter

Related Question