Enigma (http://sqljunkies.com/weblog/enigma) posted a comment on the first entry in this topic regarding another method for converting a delimited list to a table. I will show an example of it here (formatted for ease of reading):
Given the structures...
CREATE TABLE Tally (ID Int)
CREATE TABLE Quotes (Author Varchar(30), Phrase Varchar(1000))
...where table Tally is a sequential list of numbers from 1 to 8000 (you can use this script to populate it)...
DECLARE @a Int
SET @a = 1
WHILE (@a <=8000)
BEGIN
INSERT INTO Tally VALUES(@a)
SET @a = @a + 1
END
...and where Quotes has, let's say, the following data...
INSERT INTO Quotes VALUES('Shakespeare', 'A,rose,by,any,other,name,smells,just,as,sweet')
Then the following query would return each delimited value from the Phrase column as a separate row in the resulting table...
SELECT NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '')
AS Word
FROM Tally
INNER JOIN Quotes
ON ID <= Len(',' + Phrase + ',')
AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0
WHERE Author = 'Shakespeare'
Wow! Many thanks to Joe Celko for this beauty. I love how this works. This is the beauty of set-based operations. For a full explanation of how this works, go to http://www.sqlteam.com/item.asp?ItemID=2652.
Now, how would we apply this to our situation? Well, I want a UDF that will accept any delimited list and delimiter using join technique shown here. Details will be in the next post...