CODEWITHSUNDEEP
sql
RedRocket
RedRocket

Reputation: 1733

Set value to empty string (case statement)

In my stored procedure, I have a case condition in my select statement.

Like this:

select Distinct col_1, col_2, col_3, Case when (ISNULL(char_01, 'M') = 'M') 
Then
.....
Else
End

From my_table

You can see that I have a column name char_01. Let say I want to replace char_01 to 'M' then there is a null column. Also I want to replace char_01 to 'M' when there is an empty column . So far I can only check the null column (ISNULL(char_01, 'M') = 'M') but I am not sure how to also check empty column.

I have tried:

Case when (ISNULL (char_01, 'M') = 'M') or //If char_01 is empty, then replace this column as 'M'
THen .... 
Else
end

I am stuck at the second part, any help will be appreciated. Thanks

Upvotes: 0

Views: 1599

Answers (1)

artm
artm

Reputation: 8584

ISNULL(NULLIF(char_01, ''), 'M')

If char_01 is empty NULLIF will return NULL and in turn ISNULL will pick up 'M'. If char_01 IS NULL NULLIF will still return NULL and ISNULL will again pick up 'M'. If char_01 is neither NULL or empty, both NULLIF and ISNULL will return its value.

NULLIF

Sample:

create table tbl (col1 varchar(5));
insert into tbl Values (''), (' '), (NULL), ('abc'), ('NULL');

SELECT ISNULL(NULLIF(col1, ''), 'M')
FROM tbl

Upvotes: 2

Related Questions