Case with Null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Case with Null

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-10-13 : 04:55:42

I have column in which i am selecting like
select column1,column2, case(CON_CONSIGNEE_ID)
when NULL then 'False'
When '' then 'False'
When 'N/A' then 'False'
Else
'True'
End

now it is not working correctly with NULL value.How can i correct above syntax so that i can get False for values of NULL, '','N/A'

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-13 : 05:09:20

Try this:



select 
	column1,
	column2, 
	CASE
		WHEN CON_CONSIGNEE_ID NOT IN('False','')
		THEN 'True'
		ELSE 'False'
	END

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-13 : 05:34:35

quote:
Originally posted by kamii47

I have column in which i am selecting like
select column1,column2, case(CON_CONSIGNEE_ID)
when NULL then 'False'
When '' then 'False'
When 'N/A' then 'False'
Else
'True'
End

now it is not working correctly with NULL value.How can i correct above syntax so that i can get False for values of NULL, '','N/A'

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

select column1,column2,
case when CON_CONSIGNEE_ID is null then 'False'
when CON_CONSIGNEE_ID = '' then 'False'
when CON_CONSIGNEE_ID = 'N/A' then 'False'
else
'True'
End

You can also concatenate multiple condition using or
Ex:
case when CON_CONSIGNEE_ID is null or CON_CONSIGNEE_ID = '' or CON_CONSIGNEE_ID = 'N/A' then 'False'

Also try:

Also Try this and let me know whether this also works.

select column1,column2, case()
when isnull(CON_CONSIGNEE_ID,'') = '' -- I have not carried out testing
or CON_CONSIGNEE_ID = 'N/A' then 'False'
Else
'True'
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources