create function concat_ws( @separator varchar(254), @str1 varchar(1024) = null, @str2 varchar(1024) = null, @str3 varchar(1024) = null, @str4 varchar(1024) = null, @str5 varchar(1024) = null, @str6 varchar(1024) = null ) returns varchar(8000) as begin declare @cRetval varchar(8000) set @cRetval = '' if ( @str1 is not null ) set @cRetVal = @str1 if ( @str2 is not null ) set @cRetVal = @cRetval + @separator + @str2 if ( @str3 is not null ) set @cRetVal = @cRetval + @separator + @str3 if ( @str4 is not null ) set @cRetVal = @cRetval + @separator + @str4 if ( @str5 is not null ) set @cRetVal = @cRetval + @separator + @str5 if ( @str6 is not null ) set @cRetVal = @cRetval + @separator + @str6 return @cRetval endto use it:
select dbo.concat_ws( ',', 'one', '1', 'uno', default, default, default )