CREATE FUNCTION ConvertListToTable
(
@Delimiter    nvarchar(5),
@List        nvarchar(max)
)
RETURNS @TableOfValues TABLE
(
RowNumber    smallint IDENTITY(1,1),
RowValue    nvarchar(50)
)
AS
BEGIN
DECLARE @LenString int

Thank you for reading this post, don't forget to subscribe!

WHILE len( @List ) > 0
BEGIN

SELECT @LenString =
(CASE charindex( @Delimiter, @List )
WHEN 0 THEN len( @List )
ELSE ( charindex( @Delimiter, @List ) -1 )
END
)

INSERT INTO @TableOfValues
SELECT substring( @List, 1, @LenString )

SELECT @List =
(CASE ( len( @List ) – @LenString )
WHEN 0 THEN ”
ELSE right( @List, len( @List ) – @LenString – 1 )
END
)
END
RETURN
END