Setting a NULL value to an empty string in SQL

COALESCE(f3.DBWCode, '')

You can use below..

ISNULL(f3.DBWCode, '')

Heyas,

Can you be a bit more descriptive as to how I would use these?

Thank you.

Hi,

Please find the usage below.

ISNULL

SELECT Col1, ISNULL(Col2,0) Col2
FROM TableName

Select allOpen in new window

SELECT Col1, ISNULL(Col2,'') Col2
FROM TableName

Select allOpen in new window

COALESCE

SELECT Col1 , COALESCE(Col2,0) Col2
FROM TableName

Select allOpen in new window

SELECT Col1 , COALESCE(Col2,'') Col2
FROM TableName

Select allOpen in new window

Definition -

ISNULL - Replaces NULL with the specified replacement value.

ISNULL ( check_expression , replacement_value )  

From MSDN - The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

COALESCE - Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

COALESCE ( expression [ ,...n ] )  

FROM MSDN -

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

REF [ https://msdn.microsoft.com/en-in/library/ms190349.aspx ]

hmm... hang on, do you mean
If this SET statement returns a NULL value
or
If this SELECT statement returns a NULL value

Hi Snowberry,

Clarification: If this SET statement returns a NULL value.

Thank you.

May be..this

SET Environment.Variables.flag3 = ISNULL (SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 
									WHERE f3.ItemName='presendonadmit' 
									AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd) , '' )

Select allOpen in new window

Or you can check like below..after SET,...

ISNULL ( Environment.Variables.flag3 , '' )

ok, in that case your "subject" confused me

IF Environment.Variables.flag3 IS NULL THEN
    UPDATE Database.WMB_DBW_SAP_CODE as f3 SET f3.DBWCode = '' WHERE f3.ItemName='presendonadmit' 

Select allOpen in new window

Hi Pawan,

Thank for the input but I what I need is just the ISNULL to filter just on the f3.DBWCode field see below for a pseudocode example.

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

Thank you.

Hi Snowberry,

My apologies to you I just re-read my question I didn't make it 100% clear what I require is something like this:

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

How I filter just on the f3.DBWcode field for ISNULL?

Thank you.

So do you need records where f3.DBWcode IS NULL ?

may be..

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

AND f3.DBWCode IS NULL

Do you want to do the following, but in only one statement?

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd));
IF Environment.Variables.flag3 IS NULL THEN
    SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''));

Select allOpen in new window

Or may be...this

SET Environment.Variables.flag3 = 

CASE WHEN (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd)) IS NULL
THEN 
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''));
END

Select allOpen in new window

Hi Pawan,

Yes, I need the SET statement to flag when  f3.DBWCode ISNULL and change it to ''.

Thank you.

Do you want to know when  f3.DBWCode is NULL, or when Environment.Variables.flag3 is NULL ??????

Didnt get can you explain via Pesudo code?

Hi Snowberry,

When f3.DBWCode  ISNULL in the SET Statement: SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd));

My apologies for the lack of clarification initially.

Thank you.

May be this

SET Environment.Variables.flag3 = 
    	
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode , 
	
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''))

	=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd))

Select allOpen in new window

SET Environment.Variables.flag3 =
          
      (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode ,
      
      (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''))

      =InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd))

it is really unclear what you want

lets try an example

if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = 456

then your query will return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd = 456

So what problem are you trying to cope with ?

Hi Snowflake,

The best way I can clarify is if 'InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd' ISNULL change to ''.

Thank you.

Ok try this then


SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))

or you need like below

SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode,'')= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))

so

if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = 456

then you want your query to return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd IS NULL


OR


if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = ''

then you want your query to return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd IS NULL

Hi Snowflake

Correction:

so

if DBWCode equals NULL

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = NULL

Then I want DBWCode to be:

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = ''

I think SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode,'')= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))

Posted by Pawan will work I will try in the next hr.

Thanks.

are you saying you want to update the database????
your last comment does not make any sense

I think he wanted to compare these fields and WHEN both are NULL then the comparison was failing.

Now when we are replacing NULLs with '' the comparison will happen.

Thank you very much for the help guys.