ALTER FUNCTION [dbo].[fn_ConvertDelimitedStringToTable]
(
@delimitedString varchar(max),
@delimiter varchar(8) -- should only be single character in most places, but allow multiple characters just in case
)
RETURNS @result TABLE
(
rowID int IDENTITY(1,1),
value varchar(256)
)BEGINDECLARE @commaPosition int, @stringBeingProcessed varchar(max)IF LEFT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
-- the delimited string starts with the delimiter
-- remove this prefix
SET @delimitedString = SUBSTRING(@delimitedString, LEN(@delimiter) + 1, LEN(@delimitedString) - LEN(@delimiter))
ENDIF RIGHT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
-- the delimited string ends with the delimiter
-- remove this suffix
SET @delimitedString = LEFT(@delimitedString, LEN(@delimitedString) - LEN(@delimiter))
ENDIF @delimitedString IS NULL OR LEN(LTRIM(RTRIM(@delimitedString))) = 0
BEGIN
-- handle special case where a NULL or zero-length string is received
-- if this happens then return an empty result set
RETURN
ENDSET @stringBeingProcessed = @delimitedString-- Get the position of the first comma (or other delimiter char)
SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
-- If we don't have a comma then that means that we only have one value
IF @commaPosition = 0
BEGIN
-- Only one definition Name
INSERT INTO @result
VALUES (LTRIM(RTRIM(@stringBeingProcessed)))
END
ELSE
BEGIN
-- We have at least 2 values in our comma-delimited string, insert the first one
INSERT INTO @result
VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
-- Now chop the first value before the first comma
SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
-- Get the next comma position
SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
-- Loop until no commas left
WHILE @commaPosition <> 0
BEGIN
-- Insert the next value
INSERT INTO @result
VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
-- Chop the next value before the found comma
SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
-- Get the next comma position
SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
END
-- If we don't have any more commas then are on the last value
INSERT INTO @result
VALUES(LTRIM(RTRIM(@stringBeingProcessed)))
ENDRETURN END