How to convert nulls to zero 0
Microsoft SQL Server, like all other SQL servers, makes big difference between zeros 0 and nulls.
Zero 0 is a known value, that has a value of zero 0, which is different from 1, 2 or 3 or ...
Null is an unknown value, we don't know have this information yet, it could be 0, 1, 2, or 3 or...
Normally, Microsoft SQL Server, like most other SQL servers, display a value of NULL when there is a null.
However, we often want to display 0 or blank if there is a null. It's easier to read and most people will question: "what's with that null?" Most people wrongly equate a null with zero 0.
Applies to:
- Microsoft SQL Server 7
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date,sales_qty,emp_short
- FROM sales;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 2005-11-06 10:01:01.000 0.00 000110 2006-10-06 10:01:01.000 10.00 000310 2005-12-06 11:01:01.000 8.00 000010 2006-02-06 13:01:01.000 NULL 000190 2006-03-06 14:01:01.000 5.00 000110 2006-10-06 10:01:01.000 10.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-04-06 15:01:01.000 4.00 000030 2006-05-06 16:01:01.000 3.00 000010 2004-10-06 10:01:01.000 NULL 000310 2005-11-06 10:01:01.000 9.00 000010 2006-06-06 17:01:01.000 0.00 000190 2006-01-06 12:01:01.000 7.00 000310 2006-02-06 13:01:01.000 6.00 000110 2005-03-06 14:01:01.000 5.00 000190 2006-10-06 10:01:01.000 10.00 000110 2006-10-06 10:01:01.000 3.00 000110 2007-06-06 17:01:01.000 2.00 000010 2007-04-06 00:00:00.000 0.00 000010 2007-05-01 00:00:00.000 5.00 000030 2007-07-06 00:00:00.000 1.00 000190 2007-05-03 09:00:00.000 3.00 000010 2007-05-05 04:00:00.000 1.00 000010 2006-10-06 10:01:01.000 10.00 000030 2004-03-06 14:01:01.000 NULL 000110 2005-04-06 15:01:01.000 4.00 000010 2005-05-06 16:01:01.000 3.00 000190 2006-06-06 17:01:01.000 2.00 000030 2006-07-06 19:01:01.000 1.00 000310 2006-03-06 14:01:01.000 5.00 000310 2006-04-06 15:01:01.000 4.00 000010 2007-05-07 11:00:00.000 2.00 000010 2006-06-06 17:01:01.000 NULL 000110 2006-07-06 19:01:01.000 1.00 000190 2005-10-06 10:01:01.000 10.00 000030 2007-05-08 15:00:00.000 1.50 000010 2005-12-06 11:01:01.000 8.00 000190 2006-01-06 12:01:01.000 7.00 000030 2007-05-07 11:30:00.000 2.00 000030 2007-05-08 13:30:00.000 1.00 000030 2006-07-06 19:01:01.000 1.00 000010 2007-05-09 14:00:00.000 1.25 000110 2007-11-06 10:01:01.000 9.00 000030 2007-05-10 14:00:00.000 5.00 000190 2007-05-11 14:15:00.000 0.00 000190 2007-05-12 15:00:00.000 0.00 000190 2007-05-13 16:00:00.000 1.00 000190 2007-05-08 05:00:00.000 1.00 000310 2007-05-07 00:00:00.000 2.00 000310 2007-05-14 11:00:00.000 3.00 000310 2006-07-06 00:00:00.000 1.00 000190 2007-05-11 13:00:00.000 3.50 000310 2007-05-08 05:00:00.000 0.00 000010 2007-05-08 07:00:00.000 2.00 000010 2007-05-08 11:00:00.000 3.00 000010 (57 row(s) affected)
Selecting nulls
- SELECT sales_date,sales_qty,emp_short
- FROM sales
- WHERE sales_qty IS null;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2006-02-06 13:01:01.000 NULL 000190 2004-10-06 10:01:01.000 NULL 000310 2004-03-06 14:01:01.000 NULL 000110 2006-06-06 17:01:01.000 NULL 000110 (4 row(s) affected)
- Line 3: where sales_qty is null, you select the nulls with: is null.
Selecting zeros 0
- SELECT sales_date,sales_qty,emp_short
- FROM sales
- WHERE sales_qty = 0;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2005-11-06 10:01:01.000 0.00 000110 2006-06-06 17:01:01.000 0.00 000190 2007-04-06 00:00:00.000 0.00 000010 2007-05-11 14:15:00.000 0.00 000190 2007-05-12 15:00:00.000 0.00 000190 2007-05-08 05:00:00.000 0.00 000010 (6 row(s) affected)
- This is the standard selection criteria: line 3: where sales_qty = 0;
Selecting both nulls and zero 0
- SELECT sales_date,sales_qty,emp_short
- FROM sales
- WHERE sales_qty = 0 or sales_qty IS null;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2005-11-06 10:01:01.000 0.00 000110 2006-02-06 13:01:01.000 NULL 000190 2004-10-06 10:01:01.000 NULL 000310 2006-06-06 17:01:01.000 0.00 000190 2007-04-06 00:00:00.000 0.00 000010 2004-03-06 14:01:01.000 NULL 000110 2006-06-06 17:01:01.000 NULL 000110 2007-05-11 14:15:00.000 0.00 000190 2007-05-12 15:00:00.000 0.00 000190 2007-05-08 05:00:00.000 0.00 000010 (10 row(s) affected)
- You can see that it caught both the 6 rows with 0 and the 4 rows with the NULL.
Converting null to 0 during display
- SELECT sales_date,
- COALESCE(sales_qty,0) AS 'Qty w/o nulls',
- emp_short
- FROM sales
- WHERE sales_qty = 0 or sales_qty IS null;
- go
sales_date Qty w/o nulls emp_short ----------------------- --------------------------------------- ---------- 2005-11-06 10:01:01.000 0.00 000110 2006-02-06 13:01:01.000 0.00 000190 2004-10-06 10:01:01.000 0.00 000310 2006-06-06 17:01:01.000 0.00 000190 2007-04-06 00:00:00.000 0.00 000010 2004-03-06 14:01:01.000 0.00 000110 2006-06-06 17:01:01.000 0.00 000110 2007-05-11 14:15:00.000 0.00 000190 2007-05-12 15:00:00.000 0.00 000190 2007-05-08 05:00:00.000 0.00 000010 (10 row(s) affected)
- coalesce returns the first value that is NOT null.
- Line 2: coalesce(sales_qty,0) means that if sales_qty is null then assign it a value of 0 else use the value of sales_qty. It's the equivalent to a case stament.
- I could have also used: coalesce(sales_qty,-999) and MSSQL would display -999 for each null value.
Change the null values to 0 in the stored data
- UPDATE sales
- SET sales_qty = 0
- WHERE sales_qty IS null;
- go
(4 row(s) affected)
The update is done, so let's verify that it was done properly by:
- Listing all the nulls.
- Listing all the zeros 0.
Does it add up?
- SELECT sales_date,sales_qty,emp_short
- FROM sales
- WHERE sales_qty IS null;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- (0 row(s) affected)
- SELECT sales_date,sales_qty,emp_short
- FROM sales
- WHERE sales_qty = 0;
- go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2005-11-06 10:01:01.000 0.00 000110 2006-02-06 13:01:01.000 0.00 000190 2004-10-06 10:01:01.000 0.00 000310 2006-06-06 17:01:01.000 0.00 000190 2007-04-06 00:00:00.000 0.00 000010 2004-03-06 14:01:01.000 0.00 000110 2006-06-06 17:01:01.000 0.00 000110 2007-05-11 14:15:00.000 0.00 000190 2007-05-12 15:00:00.000 0.00 000190 2007-05-08 05:00:00.000 0.00 000010 (10 row(s) affected)
- It does add up, we now have 10 rows with a sale_qty of zero 0.