/* split a comma-delimited-string into a result set (array) */ if object_id('split_string_identity') is not null drop function split_string_identity go create function split_string_identity(@str nvarchar(4000)) returns @ret table(value nvarchar(512)) as begin declare @position table(i int identity, b bit) insert @position(b) select top 1000 0 from master..sysobjects a delete from @position where substring(@str, i, 1) <> ',' insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @position a join @position b on a.i < b.i group by a.i return end go select * from split_string_identity(',split,string,with,identity,table,') drop function split_string_identity ----------------------------------------------------- if object_id('split_string_position') is not null drop function split_string_position go create function split_string_position(@str nvarchar(4000)) returns @ret table(value nvarchar(512)) as begin declare @pos table(i int) declare @i int set @i = 0 while(charindex(',', @str, @i+1) > 0) begin set @i = charindex(',', @str, @i+1) insert @pos select @i end insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @pos a join @pos b on a.i < b.i group by a.i return end go select * from split_string_position(',find,comma,position,to,split,string,') drop function split_string_position ----------------------------------------------------- if object_id('split_string_position_v2') is not null drop function split_string_position_v2 go create function split_string_position_v2(@str nvarchar(4000)) returns @ret table(value nvarchar(512)) as begin declare @pos table(i int) insert @pos select 0 while(charindex(',', @str, (select max(i)+1 from @pos)) > 0) insert @pos select charindex(',', @str, (select max(i)+1 from @pos)) delete from @pos where i = 0 insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @pos a join @pos b on a.i < b.i group by a.i return end go select * from split_string_position_v2(',find,comma,position,to,split,string,version2,') drop function split_string_position_v2
将逗号分隔的string转化成array(table set)
最新推荐文章于 2023-05-10 16:16:22 发布