SQLTeam.com | Weblogs | Forums

Case When with concatenation issue


#1

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


#2

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

#3
select rtrim(r.FirstName)
+ isnull(' ' + r.MiddleInitial, '')
+ isnull(' (' + r.Nickname + ')', '')
+ rtrim(r.LastName) as "Full Name" 
From mytable r where r.EmpNo = 'XXX'

#4

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.


#5

Scott:
Thanks, worked fine and a little shorter also