ALTER FUNCTION [dbo].[Split](@InputStr nvarchar(max),@SplitBy nvarchar(20))
RETURNS @tmpTable TABLE(
ID int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
DECLARE @Text nvarchar(max)
DECLARE @iCount_InputStr int
DECLARE @ilength_InputStr int
DECLARE @SplitStrLength int
SET @SplitStrLength = LEN(@SplitBy)
IF NULLIF(@InputStr,'') IS NOT NULL
BEGIN
SET @iCount_InputStr = 1
WHILE @iCount_InputStr<>0
BEGIN
SET @iCount_InputStr = PATINDEX('%['+ @SplitBy +']%',@InputStr)
SET @ilength_InputStr = CASE @iCount_InputStr WHEN 0 THEN DATALENGTH(@InputStr) ELSE @iCount_InputStr-1 END
SET @Text = SUBSTRING(@InputStr,1,@ilength_InputStr)
INSERT INTO @tmpTable
(
value
)
VALUES
(
@Text
)
SET @InputStr = SUBSTRING(@InputStr,@iCount_InputStr+@SplitStrLength,DATALENGTH(@InputStr))
END
END
RETURN
END
