CREATE FUNCTION [dbo].[fnValueList_Split](
 @CheckValue VARCHAR (15),
 @String     VARCHAR (4000),
 @Delimiter  NVARCHAR (10)
 )
RETURNS @ValueTable TABLE ([Value] VARCHAR(4000))
AS
 
BEGIN
 DECLARE @NextString VARCHAR(4000)
 DECLARE @Pos INT
 DECLARE @NextPos INT
 DECLARE @CommaCheck NVARCHAR(1)
 
 --Initialize
 SET @NextString = ''
 SET @CommaCheck = RIGHT(@String,1) 
 
 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 SET @String = @String + @Delimiter
 
 --Get position of first Comma
 SET @Pos = CHARINDEX(@Delimiter,@String)
 SET @NextPos = 1
 
 
IF @CheckValue IS NOT NULL
	BEGIN
		INSERT INTO @ValueTable ( [Value]) VALUES (@CheckValue)
	END
 ELSE
	BEGIN
		--Loop while there is still a comma in the String of values
		WHILE (@pos <>  0)  
		  BEGIN
			  SET @NextString = SUBSTRING(@String,1,@Pos - 1)
			 
			  INSERT INTO @ValueTable ( [Value]) VALUES (@NextString)
			 
			  SET @String = SUBSTRING(@String,@pos +1,LEN(@String))
			  
			  SET @NextPos = @Pos
			  SET @pos  = CHARINDEX(@Delimiter,@String)
		  END
	END
 RETURN
END
Last modified: March 6, 2020

Author

Comments

Write a Reply or Comment