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'
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)
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)
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
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;
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...
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
