How can i replace NULL with 0.00 in TSQL

How can i write a select statment which display 0.00 for each Null value
I have a query as follows.

Select field1, field2 from table1

output
-------
1     2.25
2     1.50
3     Null
4     12.00


output should be
---------------
1     2.25
2     1.50
3     0.00
4     12.00
bcp5190Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The only problem with using a default of zero to (try to) replace NULL is that you sometimes can't tell the difference between data this is zero and is unknown.

I realize that in your situation you may not have an issue with that, but just try to think through the possibilties carefully before you're sure of that.

For example, I once worked at a place that defaulted an item's price to zero if they hadn't determined its price yet ("after all, we'll don't give any items away").  Well, later they did start allowing customers to order literature (sales brochues, etc.) free as part of an order.  It was a LOT OF WORK to correct the system to allow zero prices and still be able to identity unknown prices.

I admit that there is some additional programming required to handle nulls and that programmers generally don't like them, but if they may be needed to maintain accurate data, I say use them.
0
 
Scott PletcherSenior DBACommented:
You can do this:

Select field1, ISNULL(field2,0.00)
FROM table1

0
 
Scott PletcherSenior DBACommented:
Unfortunately, I don't know of any way to specify a global replacement value for NULL, like, for example, the SET NULL 'value' option in Oracle.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
BrightonCommented:
You could also set the default value for field2 to 0 when you created the table with

CREATE TABLE TABLE1 ( field1 NUMERIC(18,2),
field2 NUMERIC(18,2) DEFAULT 0)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Another function that ISNULL is COALESCE, with the only difference that it could accept more than 2 arguments...

CHeers
0
 
bcp5190Author Commented:
Thanks ScottPletcher.
0
All Courses

From novice to tech pro — start learning today.