DROP FUNCTION [dbo].[ufn_Split]
GO
CREATE FUNCTION [dbo].[ufn_Split](@Val varchar(max), @Del varchar(max) = ',')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(max))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(max),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint
IF @Del = 'Space'
BEGIN
SET @Del = ' '
END
SET @idx = 0
SET @Val = LTrim(RTrim(@Val))
SET @iDelimlength = DATALENGTH(@Del)
SET @bcontinue = 1
IF NOT ((@iDelimlength = 0) or (@Del = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the
first element and
--insert it with its index into the return table.
IF CHARINDEX(@Del, @Val)>0
BEGIN
SET @value = SUBSTRING(@Val,1, CHARINDEX(@Del,@Val)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the
string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @Val = LTrim(Right(@Val,DATALENGTH(@Val) - @iStrike))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @Val is the
last value in
--@retArray.
SET @value = @Val
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining
text
--instead of a delimiter. Insert the first character into
the
--retArray table. Trim the character from the front of the
string.
--Increment the index and loop.
IF DATALENGTH(@Val)>1
BEGIN
SET @value = SUBSTRING(@Val,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @Val = SUBSTRING(@Val,2,DATALENGTH(@Val)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @Val)
SET @bcontinue = 0
END
END
END
RETURN
END
No comments:
Post a Comment