Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Concat Function - SQL Server

RSS
Modified on Fri, Sep 09, 2011, 3:25 PM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.