We help IT Professionals succeed at work.

Coalesce and If Statement

I have a coalesce line in an SQL view which combines two names.  COALESCE (dbo.tbl1.Name1, N'') + N' ' + COALESCE (dbo.tbl1.Name2, N'').  Sometimes the value in dbo.tbl1.Name2 has just a single space in it ' '.  I want the coalesce line have an if statement not to include the Name2 field if it is ' '.
Comment
Watch Question

Senior Developer
BRONZE EXPERT
Commented:
E.g.

SELECT LTRIM(RTRIM(COALESCE(T.Name1, N'') + N' ' + COALESCE(T.Name2, N'')))
FROM   dbo.tbl1 T;

Open in new window

Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

You don't want to leave a trailing space either if Name2 is not present.  Therefore:


SELECT 

    COALESCE (dbo.tbl1.Name1, N'') + COALESCE(NULLIF(N' ' + dbo.tbl1.Name2, N' '), N'')

FROM dbo.table1

Explore More ContentExplore courses, solutions, and other research materials related to this topic.