String concatenation in SQL server

Question

Consider a situation we have two variables in SQL Server 2005's SP as below,

@string1 = 'a,b,c,d'
@string2 = 'c,d,e,f,g'

Is there a solution to get a new string out of that like (@string1 U @string2) without using any loops. i.e the final string should be like,

@string3 = 'a,b,c,d,e,f,g'

Answer 1

In case you need to do this as a set and not one row at a time. Given the following split function:

USE tempdb;
GO
CREATE FUNCTION dbo.SplitStrings(@List nvarchar(max))
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM
       ( SELECT Item = x.i.value(N'./text()[1]', N'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(xml, '<i>'
         + REPLACE(@List,',', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
GO

Then with the following table and sample data, and string variable, you can get all of the results this way:

DECLARE @foo TABLE(ID INT IDENTITY(1,1), col NVARCHAR(MAX));

INSERT @foo(col) SELECT N'c,d,e,f,g';
INSERT @foo(col) SELECT N'c,e,b';
INSERT @foo(col) SELECT N'd,e,f,x,a,e';

DECLARE @string NVARCHAR(MAX) = N'a,b,c,d';

;WITH x AS
(
    SELECT f.ID, c.Item FROM @foo AS f
    CROSS APPLY dbo.SplitStrings(f.col) AS c
), y AS
(
    SELECT ID, Item FROM x
    UNION
    SELECT x.ID, s.Item
        FROM dbo.SplitStrings(@string) AS s
        CROSS JOIN x
)
SELECT ID, Items = STUFF((SELECT ',' + Item 
    FROM y AS y2 WHERE y2.ID = y.ID 
    FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
FROM y
GROUP BY ID;

Results:

ID   Items
--   ----------
 1   a,b,c,d,e,f,g
 2   a,b,c,d,e
 3   a,b,c,d,e,f,x

On newer versions (SQL Server 2017+), the query is much simpler, and you don't need to create your own custom string-splitting function:

;WITH x AS
(
    SELECT f.ID, c.value FROM @foo AS f
    CROSS APPLY STRING_SPLIT
    (
      CONCAT(f.col, N',', @string), N','
    ) AS c GROUP BY f.ID, c.value
)
SELECT ID, STRING_AGG(value, N',')
WITHIN GROUP (ORDER BY value)
FROM x GROUP BY ID;

• Example db<>fiddle

Now that all said, what you really should do is follow the previous advice and store these things in a related table in the first place. You can use the same type of splitting methodology to store the strings separately whenever an insert or update happens, instead of just dumping the CSV into a single column, and your applications shouldn't really have to change the way they're passing data into your procedures. But it sure will be easier to get the data out!

EDIT

Adding a potential solution for SQL Server 2008 that is a bit more convoluted but gets things done with one less loop (using a massive table scan and replace instead). I don't think this is any better than the solution above, and it is certainly less maintainable, but it is an option to test out should you find you are able to upgrade to 2008 or better (and also for any 2008+ users who come across this question).

SET NOCOUNT ON;

-- let's pretend this is our static table:

CREATE TABLE #x
(
    ID int IDENTITY(1,1),
    col nvarchar(max)
);

INSERT #x(col) VALUES(N'c,d,e,f,g'), (N'c,e,b'), (N'd,e,f,x,a,e');

-- and here is our parameter:

DECLARE @string nvarchar(max) = N'a,b,c,d';

The code:

DECLARE @sql nvarchar(max) = N'DECLARE @src TABLE(ID INT, col NVARCHAR(32));
    DECLARE @dest TABLE(ID int, col nvarchar(32));';

SELECT @sql += '
    INSERT @src VALUES(' + RTRIM(ID) + ','''
    + REPLACE(col, ',', '''),(' + RTRIM(ID) + ',''') + ''');'
FROM #x;

SELECT @sql += '
    INSERT @dest VALUES(' + RTRIM(ID) + ','''
    + REPLACE(@string, ',', '''),(' + RTRIM(ID) + ',''') + ''');'
FROM #x;

SELECT @sql += '
    WITH x AS (SELECT ID, col FROM @src UNION SELECT ID, col FROM @dest)
    SELECT DISTINCT ID, Items = STUFF((SELECT '','' + col
     FROM x AS x2 WHERE x2.ID = x.ID FOR XML PATH('''')), 1, 1, N'''')
     FROM x;'

EXEC sys.sp_executesql @sql;
GO
DROP TABLE #x;

This is much trickier to do in 2005 (though not impossible) because you need to change the VALUES() clauses to UNION ALL...

Answer 2

How about

set @string3 = @string1+','+@string2

Sorry, wasn't clear you wanted only unique occurrences. What version of SQL server are you using? String manipulation functions vary per version.

If you don't mind a UDF to split the string, try this:

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    declare @data varchar(100)
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
       Insert Into @RtnValue (data) 
     Select ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
             Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
         Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))


    Return
END

and the code to use the UDF

go
@string1 = 'a,b,c,d'
@string2 = 'c,d,e,f,g'

declare @string3 varchar(200)
set @string3 = ''
select @string3 = @string3+data+',' 
from ( select data,min(id) as Id from dbo.split(@string1+','+@string2,',') 
     group by data ) xx
order by xx.id

print left(@string3,len(@string3)-1)

Answer 3

Found this function dbo.Split in a related answer, which you can use like this:

declare @string1 nvarchar(50) = 'a,b,c,d'
declare @string2 nvarchar(50) = 'c,d,e,f,g'

select * from dbo.split(@string1, ',')

select * from dbo.split(@string2, ',')

declare @data nvarchar(100) = ''

select @data =  @data +  ',' + Data from (
    select Data from dbo.split(@string1, ',')
    union
    select Data from dbo.split(@string2, ',')
) as d

select substring(@data, 2, LEN(@data))

The last SELECT returns

a,b,c,d,e,f,g

Answer 4

Two ways you can do that:

• Build a CLR function to do the job for you. Move the logic back to .NET code which is much easier platform for string manipulation.

• If you have to use SQL Server, then you will need to:

"explode" the two strings into two tables, this function might help: http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Get a unique list of strings from the two tables. (simple query)

"implode" the two string tables into a variable (http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string)

Answer 5

The following SQL function will convert a comma separated list to a table variable...

CREATE FUNCTION [dbo].[udfCsvToTable]( @CsvString VARCHAR( 8000))
-- Converts a comman separated value into a table variable

RETURNS @tbl TABLE( [Value] VARCHAR( 100) COLLATE DATABASE_DEFAULT NOT NULL)

AS BEGIN

DECLARE @Text VARCHAR( 100)

SET @CsvString = RTRIM( LTRIM( @CsvString))
SET @CsvString = REPLACE( @CsvString, CHAR( 9), '')
SET @CsvString = REPLACE( @CsvString, CHAR( 10), '')
SET @CsvString = REPLACE( @CsvString, CHAR( 13), '')

IF LEN( @CsvString) < 1 RETURN

WHILE LEN( @CsvString) > 0 BEGIN

    IF CHARINDEX( ',', @CsvString) > 0 BEGIN

        SET @Text = LEFT( @CsvString, CHARINDEX( ',', @CsvString) - 1)
        SET @CsvString = LTRIM( RTRIM( RIGHT( @CsvString, LEN( @CsvString) - CHARINDEX( ',', @CsvString))))
    END
    ELSE BEGIN

        SET @Text = @CsvString
        SET @CsvString = ''
    END

    INSERT @tbl VALUES( LTRIM( RTRIM( @Text)))
END

RETURN
END

You can then union the two tables together, like so...

SELECT * FROM udfCsvToTable('a,b,c,d')
UNION   
SELECT * FROM udfCsvToTable('c,d,e,f,g')

Which will give you a result set of:

a b c d e f g