(原创)SQL Server中几种特殊的“字符串到列表”的处理函数

普通的字符串分割函数已经见得很多了，写法性能也大不一样，通常都是直接基于字符串的处理，这里就不列出了。下面介绍几种特殊的方式

1.使用xml
     性能应该不如字符串处理快，没有具体测试过

IF   OBJECT_ID ( ' dbo.Split ' ) IS   NOT   NULL
    DROP   FUNCTION dbo.Split

GO
CREATE   FUNCTION dbo.Split( @data   NVARCHAR ( MAX ), @delimiter   NVARCHAR ( 5 ))
RETURNS   @t   TABLE (data NVARCHAR ( max ))
AS
BEGIN
   
    DECLARE   @textXML XML;
    SELECT     @textXML   =   CAST ( ' <d> '   +   REPLACE ( @data , @delimiter , ' </d><d> ' ) +   ' </d> '   AS XML);

    INSERT   INTO   @t (data)
    SELECT   T.split.value( ' . ' , ' nvarchar(max) ' ) AS data
    FROM     @textXML .nodes( ' /d ' ) T(split)
   
    RETURN
END
GO

DECLARE   @text   NVARCHAR ( max )
SELECT   @text   =   REPLICATE ( ' ab, ' , 300 ) +   ' ab '

SELECT   *   FROM dbo.Split( @text , ' , ' )

2. 用xml作为参数

/**/ /*    Assumes XML is as such
    <list>
        <i>1</i>
        <i>23</i>
    </list>
    etc

    Uses minimal xml markup to keep input size as small as possible
*/
ALTER   FUNCTION dbo.fnXml2IntList( @xmlList xml)
RETURNS   TABLE  
AS  
    RETURN ( SELECT tList.ListValue.value( ' . ' , ' int ' ) AS ListValue
            FROM   @xmlList .nodes( ' list/i ' ) AS tList(ListValue))
GO  

使用示例：

SELECT   *   FROM  dbo.fnXml2IntList 
(  ' <list>
        <i>1</i>
        <i>23</i>
   </list> '  
) 

3.利用笛卡尔集的方式

-- =============================================
-- Author:        代码乱了(靳如坦)
-- Create date: 2008-06-10
-- Description:    把以','分隔的字符串，转换成table
-- 示例 :select * from [dbo].[SpliteStringToList]('123,xxx,dfdsf,rer')
-- =============================================
create   Function   [ dbo ] . [ SpliteStringToList ] ( @strings   varchar ( 2000 ))  
Returns   @ReturnTable   Table (ID varchar ( max ))  
As   
Begin
    Insert   @ReturnTable
    select   substring (c, 2 , charindex ( ' , ' ,c, 2 ) - 2 ) as empno   from  
    (
        select   substring (csv.emps,iter.pos, len (csv.emps)) as c from  
        (
            select   ' , ' + @strings + ' , '   as emps
        ) csv,
        (
            -- select id as pos from t100
            -- 生产1-10000的结果集
            Select   a + b *   10   + c * 100 + d * 1000 + 1   as pos From
            ( Select   0   As a Union   Select   1   Union   Select   2   Union   Select   3   Union   Select   4   Union   Select   5   Union   Select   6   Union   Select   7   Union   Select   8   Union   Select   9 ) A,
            ( Select   0   As b Union   Select   1   Union   Select   2   Union   Select   3   Union   Select   4   Union   Select   5   Union   Select   6   Union   Select   7   Union   Select   8   Union   Select   9 ) B,
            ( Select   0   As c Union   Select   1   Union   Select   2   Union   Select   3   Union   Select   4   Union   Select   5   Union   Select   6   Union   Select   7   Union   Select   8   Union   Select   9 ) c,
            ( Select   0   As d Union   Select   1   Union   Select   2   Union   Select   3   Union   Select   4   Union   Select   5   Union   Select   6   Union   Select   7   Union   Select   8   Union   Select   9 ) d
        ) iter where iter.pos <=   len (csv.emps)
    )x
    where   len (c) >   1   and   substring (c, 1 , 1 ) =   ' , '
    Return
End
 

该函数主要运用了笛卡尔集的特点，速度上稍微慢于普通字符串处理，不过比较稳定，没有循环处理，结构上比较清晰。
需要注意到是，这个函数只能处理数组大小为10000的字符串，如果想处理更多。建议把上面的产生1-10000的结果集再扩大，并放在一个表内，这样性能肯定会有所提高，我这儿只是为了方便直接用内嵌视图，实际应用中应该先生成1-10000（根据实际应用可大可小）的数据表。如果大家发现什么问题，欢迎指正，谢谢。
 
4.运用CTE，性能应该不错，未测试性能

CREATE   FUNCTION  dbo.DelimitedSplit8K
-- ===== Define I/O parameters
( @pString   VARCHAR ( 8000 ),  @pDelimiter   CHAR ( 1 ))
RETURNS   TABLE   WITH  SCHEMABINDING  AS
RETURN
-- ===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
--  enough to cover VARCHAR(8000)
WITH  E1(N)  AS  (
SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL
SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL
SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1
),  -- 10E+1 or 10 rows
E2(N)  AS  ( SELECT   1   FROM  E1 a, E1 b),  -- 10E+2 or 100 rows
E4(N)  AS  ( SELECT   1   FROM  E2 a, E2 b),  -- 10E+4 or 10,000 rows max
cteTally(N)  AS  ( -- ==== This provides the "zero base" and limits the number of rows right up front
--  for both a performance gain and prevention of accidental "overruns"
SELECT   0   UNION   ALL
SELECT   TOP  ( DATALENGTH ( ISNULL ( @pString , 1 ))) ROW_NUMBER()  OVER  ( ORDER   BY  ( SELECT   NULL ))  FROM  E4
),
cteStart(N1)  AS  ( -- ==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT  t.N + 1
FROM  cteTally t
WHERE  ( SUBSTRING ( @pString ,t.N, 1 )  =   @pDelimiter   OR  t.N  =   0 )
)
-- ===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT  ItemNumber  =  ROW_NUMBER()  OVER ( ORDER   BY  s.N1),
Item  =   SUBSTRING ( @pString ,s.N1, ISNULL ( NULLIF ( CHARINDEX ( @pDelimiter , @pString ,s.N1), 0 ) - s.N1, 8000 ))
FROM  cteStart s
;

go
-- Test
select   *   from  dbo.DelimitedSplit8K( ' d,bb,22,11111 ' , ' , ' )