在sql中没有系统带的的字符分割函数，我们利用了一些功能字了一个分割函数有需要的同学可以参考一下。
 代码如下 复制代码
set ansi_nulls on
go
set quoted_identifier on
go
/*by kudychen 2011-9-28 */
create function [dbo].[splitstring]
(   
  @input nvarchar(max), --input string to be separated   
  @separator nvarchar(max)=',', --a string that delimit the substrings in the input string   
  @removeemptyentries bit=1 --the return value does not include array elements that contain an empty string
)
returns @table table
(   
  [id] int identity(1,1),   
  [value] nvarchar(max))
as
begin    
  declare @index int, @entry nvarchar(max)   
  set @index = charindex(@separator,@input)   
  while (@index>0)   
  begin       
    set @entry=ltrim(rtrim(substring(@input, 1, @index-1)))               
      if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry'')           
        begin               
          insert into @table([value]) values(@entry)           
        end       
        set @input = substring(@input, @index+datalength(@separator)/2, len(@input))       
        set @index = charindex(@separator, @input)   
  end       
  set @entry=ltrim(rtrim(@input))   
  if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry'')       
    begin           
      insert into @table([value]) values(@entry)       
    end   
return
end
test code:
 代码如下 复制代码
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)
set @str1 = '1,2,3'
set @str2 = '1###2###3'
set @str3 = '1###2###3###'
[value] from [dbo].[splitstring](@str1, ',', 1)
select [value] from [dbo].[splitstring](@str2, '###', 1)
select [value] from [dbo].[splitstring](@str3, '###', 0)
code:
 代码如下 复制代码
set ansi_nulls on
go
set quoted_identifier on
go
/*by kudychen 2011-9-28 */
create function [dbo].[splitstring]
(   
  @input nvarchar(max), --input string to be separated   
  @separator nvarchar(max)=',', --a string that delimit the substrings in the input string   
  @removeemptyentries bit=1 --the return value does not include array elements that contain an empty string
)
returns @table table
(   
  [id] int identity(1,1),   
  [value] nvarchar(max))
as
begin    
  declare @index int, @entry nvarchar(max)   
  set @index = charindex(@separator,@input)   
  while (@index>0)   
  begin       
    set @entry=ltrim(rtrim(substring(@input, 1, @index-1)))               
      if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry'')           
        begin               
          insert into @table([value]) values(@entry)           
        end       
        set @input = substring(@input, @index+datalength(@separator)/2, len(@input))       
        set @index = charindex(@separator, @input)   
  end       
  set @entry=ltrim(rtrim(@input))   
  if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry'')       
    begin           
      insert into @table([value]) values(@entry)       
    end   
return
end
test code:
 代码如下 复制代码
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)
set @str1 = '1,2,3'
set @str2 = '1###2###3'
set @str3 = '1###2###3###'
select [value] from [dbo].[splitstring](@str1, ',', 1)
select [value] from [dbo].[splitstring](@str2, '###', 1)
select [value] from [dbo].[splitstring](@str3, '###', 0)