閑得淡騰,今天試著自己實現了將字符串拆分為列表的功能,中間也有找一些資料,但我知道光看是沒有用的,必須要自己試著實現印象才會深刻,必須要在實現過程中遇到問題然後解決問題,反覆折騰過才算真正懂了!
拆分字符串這種數據處理功能性的代碼,寫成函數比較合適。
我寫的第一個版本(有局限):
/* 思路:遍历字符串,保存被分隔字符,遇到分隔符后播入结果表,再重新开始保存新的被分隔字符 */ if object_id('dbo.f_MySplit_1','TF') is not null drop function dbo.f_MySplit_1 go create function dbo.f_MySplit_1 ( @str varchar(1000), -- 待拆分字符串 @delimiter varchar(5) -- 分隔符(仅限一个字符) ) returns @split table(item varchar(100)) as begin set @str = @str + @delimiter -- 便于统一处理 declare @len int, @fo int, @tmp varchar(100) select @tmp = '',@len = len(@str + 'x') - 1, @fo = 1 while @fo <= @len begin if(substring(@str,@fo,1) <> @delimiter) begin set @tmp = @tmp + substring(@str,@fo,1) end else begin insert into @split values(@tmp) set @tmp = '' end set @fo = @fo + 1 end return end -- 測試 select * from testdb.dbo.MySplit_1('1,t5655565,9tt,10,a,bgfr,c',',') item ---------- 1 t5655565 9tt 10 a bgfr c (7 row(s) affected)
期本功能是實現了,可以進行常規的拆分,不過在多試驗了幾個用例之後發現,它有相當的局限性,甚至可以說是bug
1、僅支持單個分隔符
2、沒考慮 分隔符 爲 null 或 空字符 的情況(這種情況下,每一個字符被分隔為一列)
於是又做了更新,下面是第二個版本,基本上算是通用了:
/* 思路:定位前后分隔符位置,提取前后分隔之间的字符串 */ if object_id('dbo.f_MySplit_2','TF') is not null drop function dbo.f_MySplit_2 go create function dbo.f_MySplit_2 ( @str varchar(1000), -- 待拆分字符串 @split varchar(100) = '' -- 分隔符 ) returns @result table(item varchar(100)) as begin set @str = isnull(@split,'') + @str + isnull(@split,'') declare @start_index int, -- 子串搜索开始点 @split_len int, -- 分隔符长度 @fore_location int, -- 前一个分隔符的位置 @back_location int -- 后一个分隔符的位置 select @start_index = 1, @split_len = len(@split + 'x') - 1, -- len()不包含尾随空格 @fore_location = 1, @back_location = 1 if len(isnull(@split,'') + 'x') - 1 = 0 -- 若 @split 为 null 或 '' begin declare @fo int set @fo = 1 while @fo <= len(@str) begin insert into @result select substring(@str,@fo,1) set @fo = @fo + 1 end end else while (@start_index + @split_len - 1) < len(@str) begin set @fore_location = charindex(@split,@str,@start_index) set @back_location = charindex(@split,@str,@start_index + @split_len) insert into @result select substring(@str, @fore_location + @split_len, -- 取子串开始点 @back_location - @fore_location - @split_len) -- 子串长度 set @start_index = @back_location end return end -- 測試: select * from f_MySplit_2('I,@,like,@,coding',',@,') select * from f_MySplit_2('1,2,3,4','') select * from f_MySplit_2('1,2,3,4',null) select * from f_MySplit_2('1,2,3,4',default) 均能得到預期的結果(結果略)
上面代碼中兩個細節說明一下:
1、set @str = isnull(@split,'') + @str + isnull(@split,'') ,防止 @split 為空的情況,null 與任何字符串相加的結果都為 null
2、@split_len = len(@split + 'x') - 1, 為什麼不直接len((@split)呢,因為len(@string)默認不計算尾隨的空格,這樣處理一下後就變向支持了。
後來在網上看到了另一種奇特的方法,除了不支持分隔為空或null外,其餘都支持,只是不好寫為函數,可以考慮用存儲過程,真的很簡潔很強大。
與大家共享一下:
-- 巧用 union 替換分隔符,拆分字符串為列表 declare @str varchar(500), @split varchar(100), @sql varchar(800) select @str = '小明呵呵又變帥呵呵了',@split = '呵呵' set @sql = 'select ''' + replace(@str,@split,''' union all select ''') + '''' --print @sql exec(@sql) ------ 小明 又變帥 了 (3 row(s) affected)
感謝大神們的奇思妙想,感謝互聯網!!!