拆分字符串函数

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Split]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

 

/*
-----------------------------------------------------------------------------
                              《拆分字符串函数》
-----------------------------------------------------------------------------
参数：
　　　1.原字符串
      2.分隔符

返回值：根据指定分隔符，拆分字符串，返回表
-----------------------------------------------------------------------------
                                 Written by Hezison (一根火柴) 2013-03-07
-----------------------------------------------------------------------------
*/
CREATE  function dbo.Split(@string varchar(8000), @delimeter varchar(1000))
returns @tbString table(split varchar(8000)) as
begin
  declare @len int,
          @index int

  set @index = 0
  set @len = 1
  while @len > 0
    begin
      set @len = charindex(@delimeter, @string, @index)
      if @len > 0
         begin
           insert into @tbString (split) values (substring(@string, @index, @len - @index))
           set @index = @len + len(@delimeter)
         end
      else
         begin
           if @index > 0 set @index = @index - 1
           if @index <= len(@string)
              begin
                insert into @tbString (split) values (right(@string, len(@string) - @index))
              end
         end
    end
  return
end

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

调用：select * from Split('asd,fghj,kl',',')

结果：