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?