[SQL] Split 구현하기

/* 일반적인 방법 */
ALTER FUNCTION [dbo].[FN_Split] (
    @str        NVARCHAR(MAX)     -- 원본 문자열
  , @Delimeter  CHAR(1)   = '|'  -- 구분기호(Default '|')
)
RETURNS @SplitedResult TABLE (
    Idx INT IDENTITY(1,1) PRIMARY KEY -- 결과 배열 인덱스
  , Val NVARCHAR(MAX)                 -- 인덱스에 해달하는 결과 문자열
) AS
BEGIN
    -- Declare index
    DECLARE @Idx            INT
    -- Sets the index initialize
    SET @Idx = -1;

    SET @str = ISNULL(@str, '')
    SET @Delimeter = ISNULL(@Delimeter, '|')

    WHILE (LEN(@str) > 0)
    BEGIN
        SET @Idx = CHARINDEX(@Delimeter , @str) ;

        IF (@Idx = 0) AND (LEN(@str) > 0)
        BEGIN
            INSERT INTO @SplitedResult VALUES (RTRIM(LTRIM(@str))) ;
            BREAK ;
        END

        IF (@Idx > 1)
        BEGIN
            INSERT INTO @SplitedResult VALUES (RTRIM(LTRIM(LEFT(@str, @Idx - 1)))) ;
            SET @str = RIGHT(@str, (LEN(@str) - @Idx)) ;
        END
        ELSE
            SET @str = RIGHT(@str, (LEN(@str) - @Idx)) ;
    END

    RETURN
END


/* XML을 이용한 방법 */
IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split GO
CREATE FUNCTION dbo.Split(@data VARCHAR(MAX), @delimiter VARCHAR(1))
RETURNS TABLE
AS
BEGIN
    DECLARE @textXML XML;
    SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

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

Some test results:
--------------------------------
Number of Items: 100,000
SplitString Time: 46 ms
Xml2IntList Time: 983 ms
--------------------------------
Number of Items: 10,000
SplitString Time: 40 ms
Xml2IntList Time: 93 ms
--------------------------------
Number of Items: 1,000
SplitString Time: 38 ms
Xml2IntList Time: 15 ms
--------------------------------
Number of Items: 100
SplitString Time: 25 ms
Xml2IntList Time: 1.5 ms
--------------------------------