select query for sq server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

how to get the value as 0 instead of null in select query if the result value is null.

Posted 11-Jul-13 21:13pm

Maheswar Tripathy

Add a Solution

5 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)

jaideepsinh · Answer 1 · 2013-07-11T21:20:00

Solution 2

Use this in your select statement :

VB

CASE WHEN FieldName IS NULL OR FieldName=''
   THEN 0
   Else FieldName   
   End

Pls, Accept as answer and vote if solve your problem.

Posted 11-Jul-13 21:20pm

jaideepsinh

Comments

Maheswar Tripathy 12-Jul-13 3:33am

thanks for answer but it will not working getting an error....

jaideepsinh 12-Jul-13 3:40am

Can you paste you query here then i can modify as per your need.

Maheswar Tripathy 12-Jul-13 3:45am

there is table named as salary and columns are name and ctc

select case when ctc is null then 0 else ctc end from salray where name =''

jaideepsinh 12-Jul-13 3:52am

This query is correct.Can you explain what error it throw.

Maheswar Tripathy 12-Jul-13 4:09am

Conversion failed when converting the varchar value 'null' to data type int.

jaideepsinh 12-Jul-13 6:18am

It means you have to convert your varchar value to int using cast method.

**Nirav Prabtani** · Answer 2 · 2013-07-11T21:37:00

Solution 3

Try this ...:)

SQL

SELECT ISNULL(CAST(NULLIF(ColumnName, 'NULL') AS int), 0)
FROM TableName

Posted 11-Jul-13 21:37pm

Nirav Prabtani

Updated 11-Jul-13 22:39pm

v2

Comments

Maheswar Tripathy 12-Jul-13 3:46am

Dear Nirav

thanks for the answer but it gives error

Nirav Prabtani 12-Jul-13 3:47am

what is d error???

Maheswar Tripathy 12-Jul-13 4:10am

Conversion failed when converting the varchar value 'null' to data type int.

Nirav Prabtani 12-Jul-13 4:39am

try my updated solution...:)

ArunRajendra · Answer 3 · 2013-07-11T21:17:00

Solution 1

Use this. Hope it helps

ISNULL(FIELD_NAME,0)

Posted 11-Jul-13 21:17pm

ArunRajendra

Comments

Maheswar Tripathy 12-Jul-13 3:32am

aleady use this but getting an error "conversion failed while converting var-char null to 0"

ArunRajendra 12-Jul-13 4:07am

Then try this way ISNULL(@v,'0').

berrymaria · Answer 4 · 2013-07-11T22:12:00

Solution 5

Try this:

SQL

SELECT CASE WHEN columnName IS NULL THEN '0' ELSE columnName END FROM tableName

Posted 11-Jul-13 22:12pm

berrymaria

Updated 11-Jul-13 22:16pm

v2

Comments

Maheswar Tripathy 12-Jul-13 4:22am

getting an error ""Conversion failed when converting the varchar value 'null' to data type int.""

berrymaria 12-Jul-13 4:26am

Is your columnName a varchar? or what is the data type of it?

Adarsh chauhan · Answer 5 · 2013-07-11T22:45:00

Hi,
try this.

select case when ctc is null or ctc='' then '0' else ctc end from salary where name ='XYZ'

Note: Null is a varchar kind of value while 0 is int , that's why its giving error when you are writing
"case when ctc is null then 0 else ctc end"

so use it Like this

"case when ctc is null then '0' else ctc end"

Hope it will help you.. :)

select query for sq server 2005

5 solutions

Solution 2

Solution 3

Solution 1

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us