Solved

How can i replace NULL with 0.00 in TSQL

Posted on 2002-04-09
6
2,593 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 143

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 26
Acces SQL Insert Problem 6 33
where to find DTS instalation package for sql 2014 64 bit 3 12
SQLCMD Syntax 2 17
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

808 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