Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to replace string 'NULL' with null value Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:14 AM
Points: 187, Visits: 316
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.
Post #1469903
Posted Wednesday, July 3, 2013 2:34 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:07 AM
Points: 692, Visits: 2,803
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
Post #1469906
Posted Wednesday, July 3, 2013 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:14 AM
Points: 187, Visits: 316
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]
Post #1469923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse