Case in Select query in sql

Question

Below is my SQL with a case statement.

select 
    a.AssociationClubNumber,
    case a.AssociationClubNumber
        when NULL then 'NA'
        when '' then 'NA'
        else a.AssociationClubNumber
    end as 'AssociationClubNumber'
from 
    [dbo].[Customer] a

If a.AssociationClubNumber is NULL or "", then the CASE should return the string "NA".

I tried few different combinations but somehow it's not executing.

Case when AssociationClubNumber is null... – smoore4 Feb 02 '19 at 16:57 — smoore4, Feb 02 '19 at 16:57

score 1 · Accepted Answer · answered Feb 02 '19 at 16:55

Use the other form of case -- with explicit comparisons:

select c.AssociationClubNumber,
       (case when c.AssociationClubNumber is null or 
                  c.AssociationClubNumber = ''
             then 'NA'
             else c.AssociationClubNumber
        end)  as new_AssociationClubNumber
from [dbo].[Customer] c;

You are using the simple case. Even in the simple form, NULL can never match -- basically because = does not work with NULL.

Also notice:

I fixed the table alias so it makes sense (as a table abbreviation) rather than an arbitrary letter.
I changed the column alias so the two columns being returned are different.
I removed the single quotes around the column alias. Only use single quotes for string and date constants.

Great!! it executed as desired. I would mark this accept once allowed. also Many Thanks for adding other notes. Once again Many Thanks!! — Kgn-web, Feb 02 '19 at 17:00

score 0 · Answer 2 · answered Feb 02 '19 at 16:55

remove single quote from alias and use is null

 select a.AssociationClubNumber,
    Case a.AssociationClubNumber
    When is NULL THEN  'NA'  
    WHEN  '' then  'NA'
    ELSE a.AssociationClubNumber
    END  as AssociationClubNumber_val
    from [dbo].[Customer] a

score 0 · Answer 3 · edited May 30 '19 at 12:11

0

CASE [value] WHEN NULL THEN END will never evaluate to true. You would have to use CASE WHEN [value] IS NULL THEN 1 END.

For what you have though, I would use:

ISNULL(NULLIF(AssociationClubNumber,'')'NA')

edited May 30 '19 at 12:11

Zoe

27,060
21
118
148

answered Feb 02 '19 at 16:56

Thom A

88,727
11
45
75

Case in Select query in sql

3 Answers3