幾種分割字符串實現(xiàn)方法的比較

在數(shù)據(jù)庫開發(fā)中，有時會遇到把字符串，按一定規(guī)則進行分割，比如“a,b,c,1,2,3”這樣的以逗號為分隔符的字符串，需要把分割出來,分割后單獨變行一行記錄。首先想到的是使用替換函數(shù)把分隔符去掉。于是有了：

IF (OBJECT_ID(N'Tempdb..##T1') IS NOT NULL)
BEGIN
    DROP TABLE ##T1;
END
GO
DECLARE @string NVARCHAR(MAX);
SET @string=N'123,abc,456,AAA,DDD';
SET @string=N'SELECT * INTO ##T1 FROM (SELECT ''' + 
            REPLACE(@string,',',''' AS result UNION ALL SELECT ''') + ''') a' ;
EXEC(@string);
SELECT * FROM ##T1;
GO

Code-1: 方法1

該方法拼接SQL語句，簡單巧妙，但有不足。（1）拼接的SQL不夠直觀，較難編寫；（2）如果分隔符是半角的英文單引號的話，需要再作處理；（3）某些 情況下，如果字符串有中文等非英文字符，會顯示亂碼；（4）由于拼接的SQL，如果要分割的字符串很長很長，那么拼接的SQL可能會過長，而不能被執(zhí)行。 所以這種方法只能作簡單的替換或開拓思維之用。

方法2（推薦）：

IF OBJECT_ID(N'fn_split_rowno') IS NOT NULL
BEGIN
    DROP FUNCTION fn_split_rowno;
END
GO

CREATE FUNCTION fn_split_rowno
(
    @str       NVARCHAR(MAX)
   ,@split     NVARCHAR(20) = ','
)
RETURNS @t TABLE(row_no INT ,col NVARCHAR(500))
AS
BEGIN
    DECLARE @i INT
    SET @i = 0
    WHILE (CHARINDEX(@split ,@str) <> 0)
    BEGIN
        INSERT @t (row_no,col)
        VALUES(@i + 1,SUBSTRING(@str ,1 ,CHARINDEX(@split ,@str) -1))     
        SET @str = STUFF(@str ,1 ,CHARINDEX(@split ,@str) + LEN(@split) -1 ,'')
        SET @i = @i + 1 
    END  
    IF (@str <> '')
        INSERT @t (row_no,col)
        VALUES(@i + 1 ,@str)
    
    RETURN
END
GO

Code-2: 方法2（推薦）

封裝成函數(shù)，方便調(diào)用，并且不會出現(xiàn)方法1中的問題。

SELECT * FROM fn_split_rowno(N'123,abc,456,AAA,DDD,51CTO',',')

Code-3: 調(diào)用函數(shù)

方法3（來自網(wǎng)絡）：

DECLARE @string NVARCHAR(MAX)
SET @string = N'123,abc,456,AAA,DDD,博客園'
SELECT REPLACE(REVERSE((LEFT(s ,CHARINDEX(',' ,s)))) ,',' ,'') AS result
FROM   (
           SELECT r,REVERSE(LEFT(@string ,r)) + ',' AS s
           FROM   (
                      SELECT (
                                 SELECT COUNT(*)
                                 FROM   sys.objects
                                 WHERE  NAME <= t.name
                             ) AS r
                      FROM   sys.objects AS t
                  ) a
           WHERE  r <= LEN(@string) AND LEFT(@string + ',' ,r + 1) LIKE '%,'
       ) t
ORDER BY r

Code-3: 方法3

方法4（來自網(wǎng)絡）：

DECLARE @string NVARCHAR(MAX) 
SET @string = N'123,abc,456,AAA,DDD'
DECLARE @idoc INT;  
DECLARE @doc XML;  
SET @doc = CAST('<Root><item><S>' + REPLACE(@string ,',' ,'</S></item><item><S>') 
            + '</S></item></Root>' AS XML)
EXEC sp_xml_preparedocument @Idoc OUTPUT,@doc
SELECT * FROM OPENXML(@Idoc ,'/Root/item' ,2) WITH ([S] VARCHAR(10)) 
GO

Code-4: 方法4

后面兩種方法也是過于復雜，并且也有不足。