CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
user680865
user680865

Reputation: 299

case statement issue

I have a table with 2 columns have same name

ValueA  ValueB  ValueA
12      Yes     
        No      NULL
20      Yes    
0       No      0
3       No      3

Logic is that

I wrote this case statement but it gives me NULL instead of ''

Case when ValueB ='No' then convert(varchar(25), ValueA)
when ValueB ='No' and ValueA ='' then ''
else ''
end

even if I write

Case when ValueB
when 'No' then convert(varchar(25), ValueA) 
else ''
end

both of above code gives me same results

How can I replace or remove NULL with '' in second ValueA column?

Upvotes: 0

Views: 119

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

I hope i understand the question correct, you can use coalesce or isnull to replace null values.

This could be your case statement:

case when ValueB = 'Yes' then '' 
     when ValueB = 'No' then coalesce(cast(ValueA as varchar(25)), '') 
else null     end

Upvotes: 0

Tanvir Kaiser
Tanvir Kaiser

Reputation: 253

This may help:

Select
case when ValueA = '' and ValueB = 'No' then '' 
     when ValueB = 'No' then ValueA     else '' end  [ValueA]
from table

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

You need a specific case where you specify NULL as the output.

CASE WHEN ValueA = '' AND ValueB = 'No' THEN NULL
     WHEN                 ValueB = 'No' THEN convert(varchar9250, ValueA)
                                        ELSE ''
END

EDIT

I mis-understood your question then. But you can use the same approach...

CASE WHEN ValueA = '' AND ValueB = 'No' THEN ''
     WHEN                 ValueB = 'No' THEN convert(varchar9250, ValueA)
                                        ELSE ''
END


Or, as people have suggested, use ISNULL or COALESCE to remove the NULL values...

CASE WHEN ValueB = 'No' THEN ISNULL(convert(varchar9250, ValueA), '')
                        ELSE ''
END

Upvotes: 1

Related Questions