Concat Function - SQL Server

Overview¶

Building, for example, an address or a person's name from multiple fields can be tricky when some of the field values may be null or whitespace; additional delimiters between field values can easily be accidentally included. This function attempts to address those issues.

See also: Common Functions - SQL Server Reporting Services

Code¶

This function conditionally concatenates its arguments.

If both A and B are non-null and non-whitespace, the function returns a concatenation of all three arguments
If either A or B is non-null and non-whitespace, the function returns the other
Otherwise, the function returns an empty string.

create function dbo.fnConcat
    (
     @a     varchar(max)
    ,@delim varchar(max)
    ,@b     varchar(max)     
    ) returns varchar(max) as begin
    
declare 
     @result    varchar(max)
    ,@aLen      int
    ,@bLen      int

select
     @a     = RTRIM(ltrim(coalesce(@a,'')))
    ,@aLen  = LEN(@a)
    ,@b     = RTRIM(ltrim(coalesce(@b,'')))    
    ,@bLen  = LEN(@b)
    
select    
    @result = case when @aLen > 0 and @bLen > 0 then @a + @delim + @b
                when @aLen > 0 then @a
                when @bLen > 0 then @b
                else ''
                end
                
return @result
    
end