几种分割字符串实现方法的比较

多年前，我也有写博客，那时是写一些个人生活上的随笔，从没想过写跟自己工作相关的IT博客。因为网上牛人多，自己才疏学浅，所以一直在做“潜水员”，尽管自己连游泳都不会！但是我在博客园学到了许多，加上博客园简明的风格也是我喜欢的，时间长了，我也想分享一些关于IT技术方面的经验心得，也算是对自己在工作上的一些总结吧。不求有功，但求无过。如果您有更好的建议或意见，欢迎与我交流。好了，闲话休提，进入主题。

在数据库编程中，有时会遇到把字符串，按一定规则进行分割，比如“a,b,c,1,2,3”这样的以逗号为分隔符的字符串，需要把分割出来,分割后单独变行一行记录。首先想到的是使用替换函数把分隔符去掉。于是有了：

 1 IF (OBJECT_ID(N'Tempdb..##T1') IS NOT NULL)
 2 BEGIN
 3     DROP TABLE ##T1
 4 END
 5 GO
 6 
 7 DECLARE @string NVARCHAR(MAX)
 8 SET @string=N'123,abc,456,AAA,DDD'
 9 SET @string=N'SELECT * INTO ##T1 FROM (SELECT ''' + REPLACE(@string,',',''' AS result UNION ALL SELECT ''') + ''') a' 
10 EXEC(@string)
11 
12 SELECT * FROM ##T1
13 
14 GO

该方法拼接SQL语句，简单巧妙，但有不足。（1）拼接的SQL不够直观，较难编写；（2）如果分隔符是半角的英文单引号的话，需要再作处理；（3）某些情况下，如果字符串有中文等非英文字符，会显示乱码；（4）由于拼接的SQL，如果要分割的字符串很长很长，那么拼接的SQL可能会过长，而不能被执行。所以这种方法只能作简单的替换或开拓思维之用。

方法2（推荐）：

 1 IF OBJECT_ID(N'fn_split_with_rowno') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION fn_split_with_rowno
 4 END
 5 
 6 GO
 7 
 8 CREATE FUNCTION fn_split_with_rowno
 9 (
10     @str       NVARCHAR(MAX)
11    ,@split     NVARCHAR(20) = ','
12 )
13 RETURNS @t TABLE(row_no INT ,col NVARCHAR(500))
14 AS
15 BEGIN
16     DECLARE @i INT
17     SET @i = 0
18     WHILE (CHARINDEX(@split ,@str) <> 0)
19     BEGIN
20         INSERT @t (row_no,col)
21         VALUES(@i + 1,SUBSTRING(@str ,1 ,CHARINDEX(@split ,@str) -1))     
22         SET @str = STUFF(@str ,1 ,CHARINDEX(@split ,@str) + LEN(@split) -1 ,'')
23         SET @i = @i + 1 
24     END  
25     IF (@str <> '')
26         INSERT @t (row_no,col)
27         VALUES(@i + 1 ,@str)
28     
29     RETURN
30 END
31 
32 GO

封装成函数，方便调用，并且不会出现方法1中的问题。

调用函数：

1 SELECT * FROM fn_split_with_rowno(N'123,abc,456,AAA,DDD,博客园',',')

执行结果：

方法3（来自网络）：

 1 DECLARE @string NVARCHAR(MAX)
 2 SET @string = N'123,abc,456,AAA,DDD,博客园'
 3 SELECT REPLACE(REVERSE((LEFT(s ,CHARINDEX(',' ,s)))) ,',' ,'') AS result
 4 FROM   (
 5            SELECT r,REVERSE(LEFT(@string ,r)) + ',' AS s
 6            FROM   (
 7                       SELECT (
 8                                  SELECT COUNT(*)
 9                                  FROM   sys.objects
10                                  WHERE  NAME <= t.name
11                              ) AS r
12                       FROM   sys.objects AS t
13                   ) a
14            WHERE  r <= LEN(@string) AND LEFT(@string + ',' ,r + 1) LIKE '%,'
15        ) t
16 ORDER BY r

方法4（来自网络）：

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

后面两种方法也是过于复杂，并且也有不足。

几种分割字符串实现方法的比较

推荐阅读