【Wonder原创】MSSQL将格式字符串分割成Table的函数

Fun_SplitByDot

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[Fun_SplitByDot]
(
@data varchar(4000),
@char varchar(5)
)
RETURNS @tem TABLE( id int,value nvarchar(100) )
AS
BEGIN
select @data=isnull(@data,'')
if len(@data)=0
return --字符长度为0 ，退出
declare @id AS INT
select @id=1
declare @end AS INT
select @end = CHARINDEX(@char, @data)
while(@end>0)
begin
insert into @tem(id,value)
select @id,left(@data,@end-1)
select @id=@id+1
select @data=right(@data,len(@data)-@end)
select @end = CHARINDEX(@char, @data)
end
if len(@data)>0
begin
insert into @tem(id,value)
select @id,@data
end

RETURN
END

 

转载于:https://www.cnblogs.com/wonder315/archive/2010/09/28/1837690.html