Replace zero with null values

Question

Post reply

Replace zero with null values

Nuts

SSCrazy

Points: 2547
More actions
July 22, 2008 at 6:55 pm

#210264

HI guys
I have got a column in a table where there are null values for some fields..
Here is the sample data
DoctorNAme totalTest
fff

Viewing 8 posts - 1 through 8 (of 8 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Can you provide a little more information? What are you trying to do?

Nuts SSCrazy Points: 2547 More actions · Answer 2

Hi

Looks like the entire daa did not cme through

Here is it

DoctorName Test06 test08 difference

ddd 12

sss 13 666

erv 47

I want to substitute the blank values with 'zero' so that I can perform some numeric calculations.

For ex I need to find the difference in the test06 from test06 which just displays a blank value if one of the test is null.

This table has been imported from Businee Objects and its a huge one with about a million rows in it.

Hope this helps

Thanks

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

If the value is NULL you can use ISNULL. If it is blank spaces you can use the Case statement.

Declare @tst char(10)

Set @tst = NULL

Select ISNULL(@tst,0)

Set @tst = ' '

Select CASE WHEN LTRIM(RTRIM(@tst)) = '' THEN 0 ELSE @tst END

Nuts SSCrazy Points: 2547 More actions · Answer 4

Hey thanks Ken.

This query works fine but how do I modify this query to update the entire table?

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 5

Read up on ISNULL() in books online, Ken pointed you in the right direction. You might try something like this:

SELECT ISNULL(test06, 0) - ISNULL(text08, 0) AS DIFF FROM mytable

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 6

Nuts (7/22/2008)
Hey thanks Ken.
This query works fine but how do I modify this query to update the entire table?

You can also use COALESCE and NULLIF:

SELECT COALESCE(NULLIF(column, ''), 0) FROM table;

This can be used in an update query as:

UPDATE table

SET column = COALESCE(NULLIF(column, ''), 0)

WHERE column = ''

OR column IS NULL;

Make sure you test this on a copy of the table and validate the results before running.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Nuts SSCrazy Points: 2547 More actions · Answer 7

Nuts

SSCrazy

Points: 2547

July 22, 2008 at 8:55 pm

#846778

Thanks a lot guys

Query works fine now!:w00t:

Replace zero with null values

Cookies on SQLServerCentral