SQL Server中的字符串连接(String concatenation in SQL server)

考虑一下我们在SQL Server 2005的SP中有两个变量的情况如下，

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

是否有一个解决方案来获取一个新的字符串（@ string1 U @ string2）而不使用任何循环。即最后的字符串应该是，

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

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'

最满意答案

如果您需要将其作为一个集合而不是一次执行一行。鉴于以下分割功能：

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

然后使用下表和示例数据以及字符串变量，您可以通过这种方式获得所有结果：

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 DISTINCT ID, Items = STUFF((SELECT ',' + Item FROM y AS y2 WHERE y2.ID = y.ID FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, N'') FROM y;

结果：

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

现在所有人都说，你真正要做的就是遵循先前的建议，并将这些东西存放在相关的表格中。您可以使用相同类型的拆分方法在发生插入或更新时分别存储字符串，而不是仅将CSV转储到单个列中，并且您的应用程序不应该真正改变它们将数据传入的方式你的程序。但它肯定会更容易获得数据！

编辑

为SQL Server 2008添加一个更复杂的潜在解决方案，但只需少一个循环即可完成工作（使用大量表扫描并替换）。我不认为这比上面的解决方案更好，而且它当然不太可维护，但如果你发现你能够升级到2008或更高版本（以及任何2008+用户）也可以选择测试遇到这个问题）。

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

代码：

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 sp_executesql @sql; GO DROP TABLE #x;

这在2005年比较棘手（虽然并非不可能），因为你需要将VALUES()子句更改为UNION ALL ...

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('(./text())[1]', 'nvarchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List,',', '') + '').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 DISTINCT ID, Items = STUFF((SELECT ',' + Item FROM y AS y2 WHERE y2.ID = y.ID FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, N'') FROM y;

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

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 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...

更多推荐