分割字符的应用还是有不少地方的。刚好前两天在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 = 0 12 ) 13 RETURNS @t TABLE(row_no INT ,col NVARCHAR(500)) 14 AS 15 BEGIN 16 SET @row_no_start = ISNULL(@row_no_start,0) 17 WHILE (CHARINDEX(@split ,@str) <> 0) 18 BEGIN 19 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 RETURN 29 END 30 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 GO 14 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 GO 22 23 SELECT * FROM ##STU_RESULT 24 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 GO 15 16 DECLARE @r NVARCHAR(MAX) 17 DECLARE @s NVARCHAR(MAX) 18 DECLARE @name NVARCHAR(200) 19 DECLARE @no_start INT 20 21 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR 22 SELECT name FROM ##STU_RESULT 23 GROUP BY name 24 25 OPEN cur 26 FETCH NEXT FROM cur INTO @name 27 28 WHILE @@FETCH_STATUS = 0 29 BEGIN 30 SELECT @r = result,@no_start = no_start,@s = score FROM ##STU_RESULT WHERE NAME = @name 31 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_no 34 FETCH NEXT FROM cur INTO @name 35 END 36 37 CLOSE cur 38 DEALLOCATE cur 39 40 SELECT * FROM #T1 41 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 GO 15 16 IF OBJECT_ID(N'Tempdb..#T2') IS NOT NULL 17 BEGIN 18 DROP TABLE #T2 19 END 20 GO 21 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 GO 30 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_RESULT 33 --SELECT * FROM #T2 34 35 36 DECLARE @r NVARCHAR(MAX) 37 DECLARE @s NVARCHAR(MAX) 38 DECLARE @name NVARCHAR(200) 39 DECLARE @no_start INT 40 DECLARE @i INT 41 DECLARE @j INT 42 43 SET @i = 1 44 SELECT @j = MAX(id_) FROM #T2 45 46 BEGIN TRAN 47 WHILE @i <= @j 48 BEGIN 49 SELECT @name=name,@r=result,@no_start=no_start,@s=score FROM #T2 WHERE id_=@i 50 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_no 54 55 SET @i = @i + 1 56 END 57 COMMIT TRAN 58 59 SELECT * FROM #T3 60 GO
运行结果：
这两个方法有什么区别？结果看起来没什么区别，但是在我的笔记本上测试，源表插入10000条记录，运行速度，循环比游标快了几倍。至于张二为什么在张三后面，这可能是关乎于我的数据库的Collation使用的是SQL_Latin1_General_CP1_CI_AS有关了。这个问题待我有空的时候研究下。