Replace zero with null values

  • 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

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

  • 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

  • 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

  • Hey thanks Ken.

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

  • 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

  • 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

  • Thanks a lot guys

    Query works fine now!:w00t:

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

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