sqlserver拆分字符串到临时表 无 gocreate function [dbo].[split](@sourcesql varchar(max),@strseprate varchar(10))returns @temp table(line varchar(max))asbegin declare @i int set @sourcesql = rtrim(ltrim(@sourcesql)) set @i = charindex(@strsep
sqlserver拆分字符串到临时表 gocreate function [dbo].[split](@sourcesql varchar(max),@strseprate varchar(10))returns @temp table(line varchar(max))asbegin declare @i int set @sourcesql = rtrim(ltrim(@sourcesql)) set @i = charindex(@strseprate,@sourcesql) while @i >= 1 begin if len(left(@sourcesql,@i-1))>0 begin insert @temp values(left(@sourcesql,@i-1)) end set @sourcesql=substring(@sourcesql,@i+len(@strseprate),len(@sourcesql)-@i) set @i=charindex(@strseprate,@sourcesql) end if @sourcesql '' insert @temp values(@sourcesql) returnend-----测试goselect * from dbo.split('asd,f,gh,jk,l',',')
--sql 递归create function [dbo].[split](@sourcesql varchar(max),@strseprate varchar(10))returns @temp table(line varchar(max))asbegin ;with roy as ( select col2=cast(substring(@sourcesql+@strseprate,1,charindex(@strseprate,@sourcesql+@strseprate)-1) as nvarchar(max)), split=cast(substring(@sourcesql+@strseprate,charindex(@strseprate,@sourcesql+@strseprate)+1,len(@sourcesql+@strseprate)-charindex(@strseprate,@sourcesql+@strseprate)) as varchar(max)) union all select col2=cast(cast(substring(split,1,charindex(@strseprate,split)-1) as nvarchar(100)) as nvarchar(max)), split= cast(substring(split,charindex(@strseprate,split)+1,len(split)-charindex(@strseprate,split)) as varchar(max)) from roy where charindex(@strseprate,split)>1 ) insert into @temp(line)select col2 from roy option (maxrecursion 0) returnend