T-SQL拆分字符串

Question

T-SQL拆分字符串

我有一个SQL Server 2008 R2列包含一个字符串，我需要用逗号分隔。我在StackOverflow上看到了很多答案，但它们都不适用于R2。我确保我对任何拆分函数示例都有选择权限。任何帮助非常感谢。

Answer 1

我之前使用过这个SQL可能对你有用： -CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )RETURNS @returnList TABLE ([Name] [nvarchar] (500))ASBEGIN  DECLARE @name NVARCHAR(255)  DECLARE @pos INT WHILE CHARINDEX(',', @stringToSplit) > 0  BEGIN   SELECT @pos  = CHARINDEX(',', @stringToSplit)     SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)   INSERT INTO @returnList    SELECT @name  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)  END  INSERT INTO @returnList SELECT @stringToSplit RETURNEND并使用它： -SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

Answer 2

最简单的方法是使用XML格式。1.将字符串转换为不带表的行QUERYDECLARE @String varchar(100) = 'String1,String2,String3'-- To change ',' to any other delimeter, just change ',' to your desired oneDECLARE @Delimiter CHAR = ','&nbsp; &nbsp;&nbsp;SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'&nbsp;FROM&nbsp;&nbsp;(&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;) AS A&nbsp;CROSS APPLY Data.nodes ('/M') AS Split(a)结果&nbsp;x---------x&nbsp;| Value&nbsp; &nbsp;|&nbsp;x---------x&nbsp;| String1 |&nbsp;| String2 |&nbsp;| String3 |&nbsp;x---------x2.从表中转换为具有每个CSV行的ID的行消息表&nbsp;x-----x--------------------------x&nbsp;| Id&nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp;x-----x--------------------------x&nbsp;|&nbsp; 1&nbsp; |&nbsp; String1,String2,String3 |&nbsp;|&nbsp; 2&nbsp; |&nbsp; String4,String5,String6 |&nbsp; &nbsp; &nbsp;&nbsp;x-----x--------------------------xQUERY-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired oneDECLARE @Delimiter CHAR = ','SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'&nbsp;FROM&nbsp;&nbsp;(&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;FROM TABLENAME) AS A&nbsp;CROSS APPLY Data.nodes ('/M') AS Split(a)结果&nbsp;x-----x----------x&nbsp;| Id&nbsp; |&nbsp; Value&nbsp; &nbsp;|&nbsp;x-----x----------x&nbsp;|&nbsp; 1&nbsp; |&nbsp; String1 |&nbsp;|&nbsp; 1&nbsp; |&nbsp; String2 |&nbsp;&nbsp;&nbsp;|&nbsp; 1&nbsp; |&nbsp; String3 |&nbsp;|&nbsp; 2&nbsp; |&nbsp; String4 |&nbsp;&nbsp;&nbsp;|&nbsp; 2&nbsp; |&nbsp; String5 |&nbsp;|&nbsp; 2&nbsp; |&nbsp; String6 |&nbsp; &nbsp; &nbsp;&nbsp;x-----x----------x