sql split函数二款实例

分割再成批插入

declare @s varchar(8000),@sql nvarchar(4000)
set @s='1,12,1212,4545'
set @sql='insert into t(col) select '+replace(@s,',',' col union all select ')
exec(@sql)

测试
drop table #table
declare @s varchar(8000),@sql nvarchar(4000)
set @s='1,12,1212,4545,454'
create table #table (col int)
set @sql='insert into #table(col) select '+replace(@s,',',' col union all select ')
exec(@sql)
select * from #table

create function f_split(@sourcesql varchar(8000),@strseprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
as
begin
declare @i int
set @sourcesql=rtrim(ltrim(@sourcesql))
set @i=charindex(@strseprate,@sourcesql)
while @i>=1
begin
insert @temp values(left(@sourcesql,@i-1))
set @sourcesql=substring(@sourcesql,@i+1,len(@sourcesql)-@i)
set @i=charindex(@strseprate,@sourcesql)
end
if @sourcesql<>''
insert @temp values(@sourcesql)
return
end

用法：select * from dbo.f_split('abc:bc:c:d:e',':')

再看一个实例

create function [dbo].[split]
(
@str as nvarchar(1000) ,
@delimiters as nvarchar(100)
)
returns @t table
(
pos int not null ,
value nvarchar(100) not null
)
as
begin
declare @end int ,
@start int ,
@pos int ,
@delino int ,
@strchar nvarchar(1) ,
@delimiter nvarchar(1)

select @str=replace(@str,' ','　'),@delimiters=replace(@delimiters,' ','　')
set @str = ltrim(rtrim(@str))+substring(@delimiters, 1, 1)

select @pos = 1,@start = 1,@end = 1

while @end <= len(@str)
begin
select @strchar = substring(@str, @end, 1) ,
@delino = 1

while @delino <= len(@delimiters)
begin
set @delimiter = substring(@delimiters, @delino, 1)
if @strchar = @delimiter
begin
if substring(@str, @start, @end - @start) <> ''
begin
insert into @t
values ( @pos,substring(@str, @start,@end - @start) )
set @pos = @pos + 1
end
set @start = @end + 1
break
end
set @delino = @delino + 1
end
set @end = @end + 1
end
return
end
go