Post reply

Function to replace string 'NULL' with null value

  • July 3, 2013 at 2:26 am

    #276927

    Hi

    I am selecting values to one table and populating them in another table, and I'm using a case statement to check if the word is the string 'NULL' then I replace it with value null.

    e.g CASE

    WHEN [Gross_Amount] = 'NULL'

    THEN NULL

    ELSE CAST([Gross_Amount] AS DECIMAL(18,2))

    END[Gross_Amount]

    So I'm thinking I can write a function that can check to see the value if it is a string NULL, if so replace it with null or return Gross_Amount if not. and rewrite that whole CASE statement, to objective here is to minimise the lines of code.

    Can anyone help please.

  • July 3, 2013 at 2:34 am

    #1629407

    Hi

    Here is one way without using a CASE:

    DECLARE @gross_amount VARCHAR (10) = '18'

    SELECT CAST(COALESCE(NULLIF(@Gross_Amount,'NULL'),@gross_amount) AS DECIMAL(18,2))

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • July 3, 2013 at 3:04 am

    #1629421

    Thank you

    I actually thought of something else, that I could use NULLIF function then got

    CAST(NULLIF([Gross Amount],'NULL') AS DECIMAL(18,2)) [Gross Amount]

Viewing 3 posts - 1 through 2 (of 2 total)

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