修正SQL2000超长ntext的bug。

CREATE FUNCTION [dbo].[Fn_split] (@expression NTEXT,
                                  @delimiter  NVARCHAR(2) = ',')
RETURNS @expressionstable TABLE (
  [DUMMY] NVARCHAR(4000))
AS
  BEGIN
      DECLARE @currentindex INT
      DECLARE @nextindex INT
      DECLARE @returntext NVARCHAR(4000)
      DECLARE @datalength INT
      IF @expression IS NULL
        BEGIN
            INSERT INTO @expressionstable
                        ([DUMMY])
            VALUES      (NULL)
        END
      ELSE
        BEGIN
            SELECT @datalength = Datalength(@expression) / 2
            IF @datalength = 0
              INSERT INTO @expressionstable
                          ([DUMMY])
              VALUES      ('')
            ELSE
              BEGIN
                  SELECT @currentindex = 1
                  WHILE( @currentindex <= @datalength )
                    BEGIN
                        SELECT @nextindex = Charindex(@delimiter, Substring(@expression, @currentindex, 4000))
                                            + @currentindex
                        /*关键在于Charindex第二个参数最长为8000，所以每次需重新截取下一部分*/
                        IF( @nextindex = @currentindex )
                          SELECT @nextindex = @currentindex + @datalength
                        SELECT @returntext = Substring(@expression, @currentindex - 1, @nextindex - @currentindex)
                        INSERT INTO @expressionstable
                                    ([DUMMY])
                        VALUES      (@returntext)
                        SELECT @currentindex = @nextindex + 1
                    END
              END
        END
      RETURN
  END

CREATE FUNCTION [dbo].[Fn_split] (@str       VARCHAR(MAX),
                                  @separator VARCHAR(10))
RETURNS TABLE
AS
    RETURN
      (SELECT T0.DUMMY
       FROM   ( (SELECT [DUMMY] = CONVERT(XML, '<DUMMY>'
                                             + Replace(@str, @separator, '</DUMMY><DUMMY>')
                                             + '</DUMMY>')) T1
                OUTER APPLY (SELECT DUMMY = N.v.value('.', 'NVARCHAR(4000)')
                             FROM   T1.[DUMMY].nodes('/DUMMY') N(v)) T0 ))

用法：

SELECT * FROM fn_split('1,2,3,4,5', ',');