-----------------------------------------
---创建函数
create function Splitstring
(
@Sourcestr nvarchar(max),
@Splitch nvarchar(10)
)
returns @SplitTables Table
(
aaa nvarchar(max)
)
as
begin
declare @Currentindex int;
declare @Nextindex int;
declare @SplitStr nvarchar(max);
set @Currentindex=1;
while(@Currentindex<=len(@Sourcestr))
begin
select @Nextindex=charindex(@Splitch,@Sourcestr,@Currentindex);
if(@Nextindex=0 or @Nextindex is null)
select @Nextindex=len(@Sourcestr)+1;
select @SplitStr=substring(@Sourcestr,@Currentindex,@Nextindex-@Currentindex);
insert into @SplitTables (aaa) values(@SplitStr);
set @Currentindex=@Nextindex+len(@Splitch);
end
return;
end
---执行函数
select * from Splitstring('123,456,123,asd,dfgh,dfgdfg',',')
select * from Splitstring('123,;456,;12,3,;as;d,;dfgh,;dfgdfg',',;')
----删除函数
drop function Splitstring
执行结果如下: