Nida Nida - 1 year ago 70
SQL Question

How to check values in column in Select query

I need to check whether the column middle name contain any value or not.
If it is empty, then it should not be concatenated with the name.

Select
..
Agent,
FirstName + ' ' MiddleName + ' ' + LastName as Name,
...
from tbSystemUser


In above query it adds space two times in Name if MiddleName is empty.

Answer Source

You could use CASE expression with COALESCE in following:

 select
 ..
 Agent,
 case when coalesce(MiddleName, '') = '' then FirstName + ' ' + LastName 
      else FirstName + ' ' + MiddleName + ' ' + LastName 
 end as Name,
 ...
 from tbSystemUser