SQL Server 2008 从记录中的字段拆分字符串

问题描述

我有一个如下所示的数据集(输入).

I have a data set that looks like the below (the input).

IR#   CR#
1     1,2
2     3
3     4,5,6

我想要以下输出.对于本示例，您可以考虑所有字段 varchar.

I would like the following output. You can consider all fields varchar for this example.

IR#   CR#
1     1
1     2
2     3
3     4
3     5
3     6

我有 UDF 将 CSV 字符串拆分为行...但不能将表中的 1 行拆分为多行，然后联合将下一行等.

I have UDFs to split a CSV string into rows...but not something to split 1 row in a table into multiple rows and then union will the next row, etc.

谢谢！

推荐答案

使用 CROSSAPPLY 与您的拆分 UDF 结合使用.我在示例中使用的字符串拆分器来自 此处.

Use CROSS APPLY in conjunction with your splitting UDF. The string splitter I'm using for my example comes from here.

/* Create function for purposes of demo */
CREATE FUNCTION [dbo].[fnParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @parsedString TABLE (string NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END
go

/* Set up sample data */
declare @t table (
    IR int,
    CR varchar(100)
)

insert into @t
    (IR, CR)
    select 1, '1,2' union all
    select 2, '3' union all
    select 3, '4,5,6'

/* Here's the query that solves the problem */
select t.IR, p.string
    from @t t
        cross apply [dbo].[fnParseStringTSQL](t.CR,',') p


/* clean up after demo */
drop function [dbo].[fnParseStringTSQL]