分割字符串的應用

2024-07-21 02:46:43

字體：大中小

來源：轉載

供稿：網友

分割字符串的應用

分割字符的應用還是有不少地方的。剛好前兩天在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

運行結果：