Solved

How can i replace NULL with 0.00 in TSQL

Posted on 2002-04-09
6
2,553 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:ScottPletcher
ID: 6928463
You can do this:

Select field1, ISNULL(field2,0.00)
FROM table1

0
 
LVL 69

Expert Comment

by:ScottPletcher
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Accepted Solution

by:
ScottPletcher 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now