分割字符串的应用

分割字符的应用还是有不少地方的。刚好前两天在ITPUB的论坛上看有人提问这样的问题，把问题复制过来，如下：

有以下数据：

姓名 答案 开始题号 得分张一 D;C;A;B 11 5;0;3;1张二 F;A;C 13 6;0;2张三 G;H;K;D;G;B 6 8;4;1;2;3;5需求：根据姓名，在答案中根据分号相隔把答案 与得分对应分行，同一行根据开始题号逐号递增。要得到结果：姓名 答案 题号 得分张一 D 11 5张一 C 12 0张一 A 13 3张一 B 14 1张二 F 13 6张二 A 14 0张二 C 15 2张三 G 6 8张三 H 7 4张三 K 8 1张三 D 9 2张三 G 10 3张三 B 11 5

先不管数据源为什么会这样，现在只是想方法解决问题。如果要分割数据源中的“答案”和“得分”这两个字段的字符串，可参考我之前发的文章（几种分割字符串实现方法的比较），修改一下分割字符串函数即可满足分割的要求。但是这里是多记录的表，直接在SELECT语句上使用表值函数是不允许的。暂时想到有两个方法，第一是使用游标，第二是使用循环。

首先是修改后的分割函数，代码如下：

 1 IF OBJECT_ID(N'fn_split_with_rowno_set') IS NOT NULL 2 BEGIN 3     DROP FUNCTION fn_split_with_rowno_set 4 END 5 GO 6  7 CREATE FUNCTION fn_split_with_rowno_set 8 ( 9     @str       NVARCHAR(MAX)10    ,@split     NVARCHAR(20) = ','11    ,@row_no_start INT = 012 )13 RETURNS @t TABLE(row_no INT ,col NVARCHAR(500))14 AS15 BEGIN16     SET @row_no_start = ISNULL(@row_no_start,0) 17     WHILE (CHARINDEX(@split ,@str) <> 0)18     BEGIN19         INSERT @t (row_no,col)20         VALUES(@row_no_start,SUBSTRING(@str ,1 ,CHARINDEX(@split ,@str) -1))     21         SET @str = STUFF(@str ,1 ,CHARINDEX(@split ,@str) + LEN(@split) -1 ,'')22         SET @row_no_start = @row_no_start + 1 23     END  24     IF (@str <> '')25         INSERT @t (row_no,col)26         VALUES(@row_no_start ,@str)27     28     RETURN29 END30 GO

调用函数，测试下效果：

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

结果如下：

然后是插入一些测试数据。

 1 IF OBJECT_ID(N'Tempdb..##STU_RESULT') IS NOT NULL 2 BEGIN 3     DROP TABLE ##STU_RESULT 4 END 5 GO 6  7 CREATE TABLE ##STU_RESULT ( 8     name NVARCHAR(200), 9     result NVARCHAR(MAX),10     no_start INT,11     score NVARCHAR(MAX)12 )13 GO14 15 INSERT INTO ##STU_RESULT (name, result, no_start, score)16 SELECT N'张一','D;C;A;B',11,'5;0;3;1'17 UNION ALL 18 SELECT N'张二','F;A;C',13,'6;0;2'19 UNION ALL 20 SELECT N'张三','G;H;K;D;G;B',6,'8;4;1;2;3;5'21 GO22 23 SELECT * FROM ##STU_RESULT24 25 GO

运行结果：

方法1，使用游标，把每一行（每个人）的一个或多个答案和得分分割，插入到一张临时表中，或根据需要，直接更新到业务表中。

 1 --使用游标 2 IF OBJECT_ID(N'Tempdb..#T1') IS NOT NULL 3 BEGIN 4     DROP TABLE #T1 5 END 6 GO 7  8 CREATE TABLE #T1 ( 9     name NVARCHAR(200),10     result NVARCHAR(MAX),11     no_start INT,12     score NVARCHAR(MAX)13 )14 GO15 16 DECLARE @r NVARCHAR(MAX)17 DECLARE @s NVARCHAR(MAX)18 DECLARE @name NVARCHAR(200)19 DECLARE @no_start INT20 21 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR 22 SELECT name FROM ##STU_RESULT 23 GROUP BY name 24 25 OPEN cur26 FETCH NEXT FROM cur INTO @name27 28 WHILE @@FETCH_STATUS = 029 BEGIN30     SELECT @r = result,@no_start = no_start,@s = score  FROM ##STU_RESULT WHERE NAME = @name31     INSERT INTO #T1 (name,result,no_start,score)32     SELECT @name,a.col,a.row_no,b.col FROM fn_split_with_rowno_set(@r,';',@no_start) AS a 33     LEFT JOIN fn_split_with_rowno_set(@s,';',@no_start) AS b ON a.row_no = b.row_no34 FETCH NEXT FROM cur INTO @name35 END36 37 CLOSE cur38 DEALLOCATE cur39 40 SELECT * FROM #T141 GO

运行结果：

方法2，使用循环。

 1 --使用循环。 2 IF OBJECT_ID(N'Tempdb..#T3') IS NOT NULL 3 BEGIN 4     DROP TABLE #T3 5 END 6 GO 7  8 CREATE TABLE #T3 ( 9     name NVARCHAR(200),10     result NVARCHAR(MAX),11     no_start INT,12     score NVARCHAR(MAX)13 )14 GO15 16 IF OBJECT_ID(N'Tempdb..#T2') IS NOT NULL17 BEGIN18     DROP TABLE #T219 END20 GO21 22 CREATE TABLE #T2 (23     id_ INT,24     name NVARCHAR(200),25     result NVARCHAR(MAX),26     no_start INT,27     score NVARCHAR(MAX)28 )29 GO30 31 INSERT INTO #T2 (id_,name,result,no_start,score)32 SELECT ROW_NUMBER() OVER (ORDER BY name ASC),name,result,no_start,score FROM ##STU_RESULT33 --SELECT * FROM #T234 35 36 DECLARE @r NVARCHAR(MAX)37 DECLARE @s NVARCHAR(MAX)38 DECLARE @name NVARCHAR(200)39 DECLARE @no_start INT40 DECLARE @i INT41 DECLARE @j INT42 43 SET @i = 144 SELECT @j = MAX(id_) FROM #T245 46 BEGIN TRAN 47 WHILE @i <= @j 48 BEGIN49     SELECT @name=name,@r=result,@no_start=no_start,@s=score FROM #T2 WHERE id_=@i50     51     INSERT INTO #T3 (name,result,no_start,score)52     SELECT @name,a.col,a.row_no,b.col FROM fn_split_with_rowno_set(@r,';',@no_start) AS a 53     LEFT JOIN fn_split_with_rowno_set(@s,';',@no_start) AS b ON a.row_no=b.row_no54     55     SET @i = @i + 156 END57 COMMIT TRAN 58 59 SELECT * FROM #T360 GO

运行结果：

这两个方法有什么区别？结果看起来没什么区别，但是在我的笔记本上测试，源表插入10000条记录，运行速度，循环比游标快了几倍。至于张二为什么在张三后面，这可能是关乎于我的数据库的Collation使用的是SQL_Latin1_General_CP1_CI_AS有关了。这个问题待我有空的时候研究下。