After writing this function, I discovered several months later on the internet a handful of other people had done essentially the same thing, with only slightly varying methodologies. I thought I was being rather novel when I did this not only because of what it did, but also how I used it after the fact.
I'm talking about converting a delimited list into a table without using dynamic executed T-SQL. Here is my version of the UDF that does just that.
CREATE FUNCTION dbo.fnSplit2Table(@sData Varchar(8000), @sDelim Char(1))
RETURNS @tList TABLE (ListValue Sql_Variant)
AS
BEGIN
DECLARE @sTemp Varchar(1000),
@nPos Int,
@nPos2 Int
IF Len(RTrim(LTrim(@sData))) = 0
RETURN
SET @nPos = CharIndex(@sDelim, @sData, 1)
IF @nPos = 0
BEGIN
SET @sTemp = SubString(@sData, 1, Len(@sData))
INSERT INTO @tList VALUES(@sTemp)
RETURN
END
IF @nPos = Len(@sData)
BEGIN
SET @sTemp = SubString(@sData, 1, Len(@sData) - 1)
INSERT INTO @tList VALUES(@sTemp)
RETURN
END
SET @sTemp = SubString(@sData, 1, @nPos - 1)
INSERT INTO @tList VALUES(@sTemp)
WHILE @nPos > 0
BEGIN
SET @nPos2 = CharIndex(@sDelim, @sData, @nPos + 1)
IF @nPos2 = 0
SET @sTemp = SubString(@sData, @nPos + 1, Len(@sData))
ELSE
SET @sTemp = SubString(@sData, @nPos + 1, ABS(@nPos2 - @nPos - 1))
INSERT INTO @tList VALUES(@sTemp)
SET @nPos = CharIndex(@sDelim, @sData, @nPos + 1)
END
RETURN
END
I'm sure this could be optimized in a number of ways, and could also be made to trim extra spaces as well. If you have any suggestions, please add them as comments for one and all to see.
To use it, you simply supply a delimited list as the first parameter and delimiter as the second, as you see here:
SELECT * FROM dbo.fnSplit2Table('A,B,C,D,E,F,G', ',')
This will return a table with a row for each of the delimited values, as shown here:
ListValue
------------
A
B
C
D
E
F
G
In my next post, I will use this function in conjunction with a stored procedure and to show how to “page” data.