Nida Nida - 7 months ago 9
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

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 
Comments