Solved

How can i replace NULL with 0.00 in TSQL

Posted on 2002-04-09
6
2,581 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:bcp5190
6 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6928463
You can do this:

Select field1, ISNULL(field2,0.00)
FROM table1

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6928491
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
 
LVL 1

Expert Comment

by:Brighton
ID: 6929281
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 20 total points
ID: 6929473
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6930563
Another function that ISNULL is COALESCE, with the only difference that it could accept more than 2 arguments...

CHeers
0
 

Author Comment

by:bcp5190
ID: 6931166
Thanks ScottPletcher.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question