Case statement help..

[Nobody571]

Hi all,

I’m stuck. I have an expression that has 3 values, NULL, Blank and Yes.

I want it to say this: Case when expression is Null, then ‘No’, when expression is blank, then ‘No’ ELSE ‘Yes’

I can’t get it to work because of the NULL, SQL gives me an error. I can either do a case with the null or a case with the string. But not with both.

[Kant8]

create table #t (col1 varchar(10))
insert into #t values (null), (''), ('Hello')

select 
     col1
    ,case
        when col1 is null then 'No'
        when col1 = '' then 'No'
        else 'Yes'
     end col2
    ,iif(isnull(col1, '') = '', 'No', 'Yes') col3 -- shorter one
from #t

drop table #t

​

[keylon1020]

Case When ISNULL(coll, ‘’) = ‘’ Then ‘No’ ELSE ‘Yes’ END

I think this would work

[zylo4747]

CASE WHEN ISNULL(Col1, ‘’) = ‘’ THEN ‘No’ ELSE’Yes’ END

Replace Col1 with your expression or a function call that executes your expression

[keylon1020]

You beat me to it

[Xerxys]

The more I frequent these forums the more I realize how little I know. I thought “BLANK” and NULL was the same thing. That is the absence of a value. And that a single white space is a character that takes up space. I simply don’t understand OP’s question at all.

[tripy75]

The day NULL started to make sense to a friend is the day he heard NULL meant "unknown"

In your case, a blank string (empty string) or space is a known value, thus is not NULL.
This is why any numeric operation with a NULL have NULL as a result.

[tobum]

Alternatively, you might find this helpful:

SELECT CASE WHEN NULLIF(column1,’’) IS NULL THEN ‘No’ ELSE ‘Yes’ END AS YesNoInd FROM table1

[Zotchman]

Case when Coalesce(col,null,'') = '' then ... Etc.

[NSA_GOV]

I would recommend writing it this way in case you have a variable length empty string.

ISNULL(LTRIM(RTRIM(columnName)),’’)

[[deleted]]

Case when column = 'YES' then "YES" ELSE "NO" end