CREATE FUNCTION [dbo]. [Fn_splitstringtotable] (@p_Input VARCHAR (MAX), @p_Delimeter CHAR (1) =',') RETURNS @l_Table Table (Id INT IDENTITY (1,1), Value VARCHAR (511), Position int, Length int) BEGIN DECLARE @l_Position int, @l_Value VARCHAR ( the) SELECT @l_Position=1, @l_Value="'IF Right (@p_Input, LEN (@p_Delimeter))<>@p_Delimeter SET @p_Input= @p_Input +@p_Delimeter while SUBSTRING (@p_Input, @l_Position,1) = CHAR (Ten) OR SUBSTRING (@p_Input, @l_Position,1) = CHAR ( -) BEGIN SELECT @l_Position= @l_Position +1END while @l_Position<=CHARINDEX (@p_Delimeter, @p_Input, @l_Position) BEGIN SET @l_Value=RTRIM (LTRIM (SUBSTRING (@p_Input, @l_Position, CHARINDEX (@p_Delimeter, @p_Inp UT, @l_Position)-@l_Position)) ) IF not EXISTS (SELECT1From @l_Table WHERE Value=@l_Value) BEGIN INSERT into @l_Table (Value, Position, Length) VALUES (@l_Value, @l_Position, CHARINDEX (@p _delimeter, @p_Input, @l_Position)-@l_Position) END SELECT @l_Position= CHARINDEX (@p_Delimeter, @p_Input, @l_Position) +1while SUBSTRING (@p_Input, @l_Position,1) = CHAR (Ten) OR SUBSTRING (@p_Input, @l_Position,1) = CHAR ( -) BEGIN SELECT @l_Position= @l_Position +1End End Returnend
1 SQL SERVER implements a method for splitting strings into table