时间:2021-07-01 10:21:17 帮助过:49人阅读
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拆分字符串到临时表 <无>GO Create function [dbo].[split] ( @SourceSql varchar(max), @StrSeprate varchar(10) ) returns @temp table(line varchar(max)) as begin 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) return end -----测试 GO Select * 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)) as begin ;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) return end