SQL里类似SPLIT的分割字符串函数

方法一：

use study SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:  cxw -- Create date: 2006/11/28 -- Description: 用递归实现字符串分割 /* --函数具体用法： --最常用的调用： select * from dbo.RecurrentSplit('AA@BBB@CCC@','@',0,0)

select * from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',null,null)--默认

--具体细节用法： --分割字符不显示，分割方法是按前面的分割字符串,只显示分割后的字符串 select * from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',0,1) --分割字符显示,并是按前面的来进行分割，分割字符放在被分割字符串的前面 select * from dbo.RecurrentSplit('@@@AA@@@BBB@@@CCC@@@','@@@',1,1) --分割字符不显示,并是按后面的来进行分割，只显示分割后的字符串 select * from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',0,0) --分割字符显示,并是按后面的来进行分割，分割字符放在被分割字符串的后面 select * from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',1,0) */ -- ============================================= Create FUNCTION DBO.RecurrentSplit ( @nvStr nvarchar(2000)  --需要分割字符串 ,@vSeparte varchar(50)  --分割字符串 ,@iIsHaveSeparte int   --是否显示字符串 ,@iIsBefore int    --是否是后面的分割符（分割字符分割的顺序） ) RETURNS @Split table ( IndexNo int default(0)   --流水号 ,SplitName nvarchar(1000)  --分割后字符串 ) AS BEGIN  if(charindex(@vSeparte,@nvStr)<=0) --处理在整个字符串里都没有要分割，也就是字符串本身  begin   insert into @Split(SplitName) values(@nvStr)   return  end  declare @iSeparteLen int  ,@iEndHave int --最后几个字符串是否是分割字符  ,@iStartHave int --前面几个字符串是否是分割字符  select @iSeparteLen=len(@vSeparte)  ,@iStartHave=0  ,@iEndHave=0  ,@iIsHaveSeparte=case when @iIsHaveSeparte is null --默认值   then 0   else @iIsHaveSeparte   end  ,@iIsBefore=case when @iIsBefore is null --默认值   then 0   else @iIsBefore   end  if(@iIsBefore=1) --只有在处理前面字符串分割时才用  begin   if(left(@nvStr,@iSeparteLen)<>@vSeparte)--处理前面几个分割字符一定是分割字符，不是就加   begin    select @nvStr=@vSeparte+@nvStr    ,@iStartHave=1   end  end  if(right(@nvStr,@iSeparteLen)<>@vSeparte)--处理最后几个分割字符一定是分割字符，不是就加  begin   select @nvStr=@nvStr+@vSeparte   ,@iEndHave=1  end; --分号一定不能少，因为用了with,是用公用表达式，在其前面一定要加;    with CharCET(CharStr,StrLen,IndexNo)  as  (   select substring(@nvStr,0   ,case when @iIsBefore=0 then charindex(@vSeparte,@nvStr)+@iSeparteLen   else charindex(@vSeparte,@nvStr,charindex(@vSeparte,@nvStr)+@iSeparteLen) end   ) CharStr   ,case when @iIsBefore=0 then charindex(@vSeparte,@nvStr)   else charindex(@vSeparte,@nvStr,charindex(@vSeparte,@nvStr)+@iSeparteLen) end StrLen   ,0 IndexNo  --第一次初始化   union all   select substring(@nvStr   ,case when @iIsBefore=0 then StrLen+@iSeparteLen   else StrLen end   ,charindex(@vSeparte,@nvStr,StrLen+@iSeparteLen)-StrLen) CharStr    ,charindex(@vSeparte,@nvStr,StrLen+@iSeparteLen) StrLen   ,IndexNo+1 IndexNo --进行递归分割字符串   from CharCET   where StrLen<len(@nvStr)-@iSeparteLen --处理递归结束语句，也就是不要让其生成无限弟归下去  )  insert into @Split(IndexNo,SplitName)  select IndexNo,case when @iIsHaveSeparte=0   then replace(CharStr,@vSeparte,'')   else CharStr   end CharStr  from CharCET   if(@iIsHaveSeparte=1) --是否显示分割字符串  begin   update @Split --处理前面的分割字符串   set SplitName=case when @iStartHave=1    then replace(SplitName,@vSeparte,'')    else SplitName    end   where IndexNo = 0

  update @Split --处理后面的分割字符串   set SplitName=case when @iEndHave=1    then replace(SplitName,@vSeparte,'')    else SplitName    end   where IndexNo = (select Max(IndexNo) from @Split)  end  RETURN END GO 

方法二：

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

SQL里类似SPLIT的分割字符串函数

T- SQL对字符串的处理能力比较弱，比如我要循环遍历象1,2,3,4,5这样的字符串，如果用数组的话，遍历很简单，但是T- SQL不支持数组，所以处理下来比较麻烦。下边的函数，实现了象数组一样去处理字符串。 一,用临时表作为数组 create   function   f_split(@c   varchar(2000),@split   varchar(2))      returns   @t   table(col   varchar(20))      as        begin              while(charindex(@split,@c)<>0)            begin              insert   @t(col)   values   (sub string(@c,1,charindex(@split,@c)-1))              set   @c   =   stuff(@c,1,charindex(@split,@c),'')            end          insert   @t(col)   values   (@c)          return        end      go          select   *   from   dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')          drop   function   f_split     col                                          --------------------        dfkd      dfdkdf      dfdkf      dffjk          （所影响的行数为   4   行） 二、按指定符号分割字符串，返回分割后的 元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。 CREATE function Get_StrArrayLength ( @str varchar(1024),  --要分割的字符串 @split varchar(10)  --分隔符号 ) returns int as begin declare @location int declare @start int declare @length int set @str=ltrim(rtrim(@str)) set @location=charindex(@split,@str) set @length=1 while @location<>0 begin    set @start=@location+1    set @location=charindex(@split,@str,@start)    set @length=@length+1 end return @length end 调用 示例：select dbo.Get_StrArrayLength('78,1,2,3',',') 返回值：4 三、按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便 CREATE function Get_StrArrayStrOfIndex ( @str varchar(1024),  --要分割的字符串 @split varchar(10),  --分隔符号 @index int --取第几个元素 ) returns varchar(1024) as begin declare @location int declare @start int declare @next int declare @seed int set @str=ltrim(rtrim(@str)) set @start=1 set @next=1 set @seed=len(@split) set @location=charindex(@split,@str) while @location<>0 and @index>@next begin    set @start=@location+@seed    set @location=charindex(@split,@str,@start)    set @next=@next+1 end if @location =0 select @location =len(@str)+1 --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。 return substring(@str,@start,@location-@start) end 调用示例：select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2) 返回值：9 三、结合上边两个函数，象数组一样遍历字符串中的元素 declare @str varchar(50) set @str='1,2,3,4,5' declare @next int   set @next=1 while @next<=dbo.Get_StrArrayLength(@str,',') begin print dbo.Get_StrArrayStrOfIndex(@str,',',@next) set @next=@next+1 end 调用结果： 1 2