[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 --创建函数(第三版)(dawugui(爱新觉罗.毓华)) create function [dbo].[d_split] (@inputstr varchar(8000),@seprator varchar(10)) returns @temp table (a varchar(200)) as begin declare @i int set @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) end if @inputstr <> '/' insert @temp values(@inputstr) return end --测试示例 declare @sql varchar(20) set @sql='A,B,C,D,E' select * from dbo.m_split(@sql,',') select * from dbo.f_split(@sql,',') select * from dbo.d_split(@sql,',') --运行结果(结果是相同的) /* col --------- A B C D E */