Database custom table-valued functions split (@LongStr, @SplitStr, @IsDistinct)

CREATE FUNCTION [dbo]. [F3_split] (@LongStr varchar (MAX), @SplitStr varchar, @IsDistinct BIT)
RETURNS @RTable TABLE (ID INT IDENTITY PRIMARY key,shortstr VARCHAR (MAX))
As BEGIN
IF ISNULL (@LongStr, ') = '--the expression is empty or null returns an empty table directly
RETURN
IF ISNULL (@SplitStr, ') = '--delimiter is empty returns a table of records (holding an expression)
BEGIN
INSERT into @RTable (shortstr) SELECT @LongStr
RETURN
END
DECLARE @Split_len INT
DECLARE @LongStr_len INT
SET @Split_len =len (@SplitStr)-The length of the delimiter
SET @LongStr_len =len (@LongStr)--The length of the current expression
While CHARINDEX (@SplitStr, @LongStr) >0
BEGIN
DECLARE @ShortStr VARCHAR (1000)--split string
SET @ShortStr =substring (@LongStr, 1,charindex (@SplitStr, @LongStr)-1-each time a delimiter is taken from the 1th position-1 is a section
SET @LongStr =substring (@LongStr, CHARINDEX (@SplitStr, @LongStr) [email Protected]_len, (@LongStr_len-len (@ShortStr) [ Email protected]_len)--the value of the transform expression is the remaining
SET @LongStr_len =len (@LongStr)--The length of the transform expression
IF @ShortStr <> ' and @IsDistinct =1
INSERT into @RTable (shortstr) SELECT @ShortStr
Where not EXISTS (SELECT 1 from @RTable WHERE[email protected])--Put the split string into the table
ELSE IF @ShortStr <> '
INSERT into @RTable (shortstr) SELECT @ShortStr
END
If @LongStr <> ' and @IsDistinct = 1--If the last section has no delimiters, go directly into the table
INSERT into @RTable (shortstr) SELECT @LongStr
Where not EXISTS (SELECT 1 from @RTable WHERE[email protected])
ELSE IF @LongStr <> '
INSERT into @RTable (shortstr) SELECT @LongStr
RETURN
END

Database custom table-valued functions split (@LongStr, @SplitStr, @IsDistinct)