Case When with concatenation issue

select rtrim(r.FirstName)

  • case when rtrim(r.MiddleInitial) IS NULL then '' else ' ' + rtrim(r.MiddleInitial) + ' ' end
  • case when rtrim(r.Nickname) IS NULL then '' else '(' + r.Nickname + ') ' end
  • rtrim(r.LastName) as "Full Name"
    From mytable r where r.EmpNo = 'XXX'

When I run this I get something like
Bill M () Eddings
when there is no nickname. When I do have a nickname then it works like I want it to, like
Bill M (Bubba) Eddings.

How do I correct this so that I get
Bill M () Eddings
when there is no nickname?

Thanks

I think you need to change to also check for empty string

case when rtrim(r.Nickname) IS NULL OR r.Nickname = '' then '' else '(' + r.Nickname + ') ' end
select rtrim(r.FirstName)
+ isnull(' ' + r.MiddleInitial, '')
+ isnull(' (' + r.Nickname + ')', '')
+ rtrim(r.LastName) as "Full Name" 
From mytable r where r.EmpNo = 'XXX'
1 Like

It looks like Nickname is not a NULL value - but instead is an empty string. Taking Scott's approach and adding NULLIF should do the trick.

Scott:
Thanks, worked fine and a little shorter also