[MSSQL]字符分割字符串的三种写法

 

go

--创建函数(第一版)

create function [dbo].[m_split](@c varchar(2000),@split varchar(2)) 

returns @t table(col varchar(200)) 

as 

begin 

while(charindex(@split,@c)<>0) 

begin 

insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) 

set @c = stuff(@c,1,charindex(@split,@c),'') 

end 

insert @t(col) values (@c) 

return 

end


--下面两种是在论坛看到高手们发的

go

--创建函数(第二版)(fredrickhu(小F))

create function [dbo].[f_split](@s varchar(8000), @split varchar(10) )

returns table

as

return

(select substring(@s,number,charindex(@split,@s+@split,number)-number)as col

from master..spt_values

where type='p' and number<=len(@s+'a') 

and charindex(@split,@split+@s,number)=number)


go

--创建函数(第三版