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