原贴地址：http://www.cnblogs.com/jintan/archive/2008/06/15/1222528.html

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

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

-- =============================================
-- 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.运用WITH AS，递归
待续。。。。