Change 0 to a null in a query

Question

Post reply

Change 0 to a null in a query

chriej1

SSC-Addicted

Points: 409
More actions
December 18, 2009 at 4:40 pm

#213821

Hello,
I have a DB that i don't need to write much code for but have been asked to change a 0 to a NULL value for a report. while i know this has to be fairly simple i just can't seem to find the clause that will do it.
I know how to change NULL to a new value "isnull(table.field, new value)" but how do you do the reverse "value(table.field, null)"
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Lookup NULLIF...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chriej1 SSC-Addicted Points: 409 More actions · Answer 2

I knew it would be simple. Thanks for helping the newb:-)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Actually, thank you. Lot's of people just want to be spoonfed instead of taking the time to spend a bit of time with BOL. It's a pleasure to see something different. 🙂

As a side bar, you are brand spanking new to the forum so welcome aboard! In case you want or need to ask more detailed questions, take a look at the article at the first link in my signature line below. People who take the time to post proper table creation statements and readily consumable data as outlined in that article find people crawling over each other to help answer the question/problem for you with fully tested code.

Again, welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cthariharan Say Hey Kid Points: 698 More actions · Answer 4

This is working for me...

syntax - CASE WHEN e1=e2 THEN NULL ELSE e1 END

ex1 - select case when clientname = '' then NULL else 'a1' end from abc

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/