Create FUNCTION [dbo].[Split] --不返回空行
(
@SplitString VARCHAR(8000),-- nvarchar(4000)
@Separator VARCHAR(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] INT IDENTITY(1,1),
[value] VARCHAR(8000)-- nvarchar(4000)
)
AS
BEGIN
DECLARE @CurrentIndex INT;
DECLARE @NextIndex INT;
DECLARE @ReturnText VARCHAR(8000);-- nvarchar(4000)
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=LEN(@SplitString))
BEGIN
SELECT @NextIndex=CHARINDEX(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=LEN(@SplitString)+1;
SELECT @ReturnText=SUBSTRING(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value])
VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
CREATE function [dbo].[f_split](@c varchar(max),@split varchar(2)) --返回空行
returns @t table(col varchar(8000))
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