SQL中字符串根据分隔符，返回多列

/*------------------------------------------------------------  
*创建人：   Cynosure
*描述：    根据分隔符“,”，返回两列  
------------------------------------------------------------*/  

CREATE   FUNCTION [dbo].[f_splitcol](@c   varchar(8000),@c1 varchar(8000),@split   varchar(2))   
  RETURNS   @t   table(col   varchar(1000),col1 varchar(1000))   
  as   
    BEGIN   
    
      WHILE(charindex(@split,@c)<>0)   
        BEGIN   
          INSERT   @t(col,col1)   VALUES   (substring(@c,1,charindex(@split,@c)-1),substring(@c1,1,charindex(@split,@c1)-1))   
          SET   @c   =   stuff(@c,1,charindex(@split,@c),'') 
          SET   @c1  =   stuff(@c1,1,charindex(@split,@c1),'') 
        END   
      INSERT   @t(col,col1)   VALUES   (@c,@c1)   
      RETURN   
    END   

一个字符串的分割：

CREATE function [dbo].[split]
(
  @SourceSql varchar(8000),
  @StrSeprate varchar(10)
)
returns @temp table(col varchar(100))
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

 

 

转载于:https://www.cnblogs.com/Cynosure/archive/2012/04/09/2439162.html