how to set null to zero in sql server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Hello

I have a table and on that table various fields are there some fields are null.

Suppose i want to fetch all the value like

select * from tablename

It fetch all the value.

I need that the value which are null it will replace to zero and i don't know which fields are null. I want to fetch query like : select * from tablename.

If any solution then please help.

Posted 20-Feb-13 17:43pm

Jimut123456789

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2013-02-20T17:57:00

Hi,

you can make use of ISNULL function of SQL. ISNULL function will replace the null value with the value you specify.
refer below article for more on ISNULL function.
ISNULL-MSDN[^]

SQL

--Example,
SELECT ISNULL(col1, 0), ISNULL(col2, 0), ISNULL(col3, ''), ISNULL(col4, GETDATE()) FROM TableName

also, i suggest, using "*" in select query is not good practice. specify the column names.

hope it helps.

Ksh_Sr · Accepted Answer · 2013-02-20T18:02:00

Solution 2

Make use ISNULL keyword to make null columns return value as 0.
For ex:

SELECT ISNULL(column1,0) , ISNULL(column2,0) FROM TABLENAME

cheers. :)

Posted 20-Feb-13 18:02pm

Ksh_Sr

MalwareTrojan · Accepted Answer · 2013-02-20T18:02:00

Solution 3

Select Nvl(ColName,0) As ColNAme From TabName

ColName: Column Name, if it is Null then 0 will be displayed else its value will be displayed.

TabName: Table Name

Posted 20-Feb-13 18:02pm

MalwareTrojan

Comments

MalwareTrojan 21-Feb-13 0:03am

This is for PL/Sql or Oracle.

gvprabu · Accepted Answer · 2013-02-20T18:17:00

Solution 4

Hi,

U can use

SQL

ISNULL(), COALESCE()

Functions.

Check the following links

SQL NULL Functions[^]

Deciding between COALESCE and ISNULL in SQL Server[^]

ISNULL() VS. COALESCE()[^]

Posted 20-Feb-13 18:17pm

gvprabu