SQL 2005 在分隔符上拆分逗号分隔列 [英] SQL 2005 Split Comma Separated Column on Delimiter

问题描述

我的谷歌搜索关于如何在分隔符上拆分字符串产生了一些有用的函数，用于在字符串已知时拆分字符串(即见下文):

My google searches on how to split a string on a delimiter have resulted in some useful functions for splitting strings when the string is known (i.e. see below):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))     
   returns @temptable TABLE (items varchar(8000))       
   as       
   begin       
       declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

       while @idx!= 0       
       begin       
           set @idx = charindex(@Delimiter,@String)       
           if @idx!=0       
               set @slice = left(@String,@idx - 1)       
           else       
              set @slice = @String       

           if(len(@slice)>0)  
               insert into @temptable(Items) values(@slice)       

           set @String = right(@String,len(@String) - @idx)       
           if len(@String) = 0 break       
       end   
   return       
   end  

这适用于已知字符串，例如:

This works well for a known string like:

SELECT TOP 10 * FROM dbo.Split('This,Is,My,List',',')

但是，我想将一列传递给一个函数，并将它与我自己行中的其他数据结合在一起......例如给定数据:

However, I would like to pass a column to a function, and have it unioned together with my other data in it's own row... for example given the data:

CommaColumn   ValueColumn1   ValueColumn2
-----------   ------------   -------------
ABC,123       1              2
XYZ, 789      2              3

我想写一些类似的东西:

I would like to write something like:

SELECT Split(CommaColumn,',') As SplitValue, ValueColumn1, ValueColumn2 FROM MyTable

然后回来

SplitValue    ValueColumn1   ValueColumn2
----------    ------------   ------------
ABC           1              2
123           1              2
XYZ           2              3
789           2              3

这可能吗，或者之前有人做过吗?

Is this possible, or has anyone done this before?

推荐答案

是的，使用 CROSS APPLY (SQL 2005+) 是可能的:

Yes, it's possible with CROSS APPLY (SQL 2005+):

with testdata (CommaColumn, ValueColumn1, ValueColumn2) as (
  select 'ABC,123', 1, 2 union all
  select 'XYZ, 789', 2, 3
  ) 
select 
  b.items as SplitValue
, a.ValueColumn1
, a.ValueColumn2
from testdata a
cross apply dbo.Split(a.CommaColumn,',') b

注意事项:

1. 您应该向拆分列的结果集中添加一个索引，以便它返回两列，IndexNumber 和 Value.

1. You should add an index to the result set of your split column, so that it returns two columns, IndexNumber and Value.

带有数字表的内嵌实现通常比此处的程序版本更快.

In-line implementations with a numbers table are generally faster than your procedural version here.

例如:

create function [dbo].[Split] (@list nvarchar(max), @delimiter nchar(1) = N',')
returns table
as
return (
  select 
    Number = row_number() over (order by Number)
  , [Value] = ltrim(rtrim(convert(nvarchar(4000),
        substring(@list, Number
        , charindex(@delimiter, @list+@delimiter, Number)-Number
        )
    )))
  from dbo.Numbers
  where Number <= convert(int, len(@list))
    and substring(@delimiter + @list, Number, 1) = @delimiter
  )

Erland Sommarskog 对此有明确的页面，我认为:http://www.sommarskog.se/arrays-in-sql-2005.html

Erland Sommarskog has the definitive page on this, I think: http://www.sommarskog.se/arrays-in-sql-2005.html

这篇关于SQL 2005 在分隔符上拆分逗号分隔列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！