分割字符串的应用-CSDN博客

分割字符的应用还是有不少地方的。刚好前两天在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