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    begin 
    select  @strchar = substring(@str, @end, 1) ,
      @delino = 1
          
    while @delino      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