Hello Simplicio,

This user defined function will take three parameters: the string you wish to split, the delimiter for the split, and a numeric value for an id column. I use this function to create a relational index/dictionary and should be simple to modify:

CREATE FUNCTION SplitIntoTable (@InString VARCHAR(5000), @DELIMITER CHAR(1), @Item_id int)
RETURNS @OutTable TABLE (item_id int,StringValues VARCHAR(255))
AS
BEGIN
DECLARE @pos INT
DECLARE @TempStr VARCHAR(100)

SET @InString=REPLACE(@InString,CHAR(13),'')
SET @InString=REPLACE(@InString,CHAR(10),' ')
SET @InString=REPLACE(@InString,'''s',' ')
SET @InString=REPLACE(@InString,'''S',' ')
SET @InString=REPLACE(@InString,',',' ')
SET @InString=REPLACE(@InString,'.',' ')
SET @InString=REPLACE(@InString,';',' ')
SET @InString=REPLACE(@InString,':',' ')
SET @InString=REPLACE(@InString,'!',' ')
SET @InString=REPLACE(@InString,'“',' ')
SET @InString=REPLACE(@InString,'”',' ')
SET @InString=REPLACE(@InString,'''''',' ')
SET @InString=REPLACE(@InString,'`',' ')
SET @InString=REPLACE(@InString,'?',' ')
SET @InString=REPLACE(@InString,'(',' ')
SET @InString=REPLACE(@InString,')',' ')
SET @InString=REPLACE(@InString,'[',' ')
SET @InString=REPLACE(@InString,']',' ')
SET @InString=REPLACE(@InString,'/',' ')
SET @InString=REPLACE(@InString,'\',' ')
SET @InString=REPLACE(@InString,'"',' ')
SET @InString=REPLACE(@InString,'’S',' ')
SET @InString=REPLACE(@InString,'’s',' ')
SET @InString=REPLACE(@InString,'-',' ')
SET @InString=REPLACE(@InString,'—',' ')

WHILE CHARINDEX(' ',@InString,1)>0
BEGIN
SET @InString=REPLACE(@InString,' ',' ')
END

SET @InString = RTRIM(LTRIM(@InString))
SET @pos = CHARINDEX(@DELIMITER,@InString,1)

WHILE @pos <> 0
BEGIN
SET @TempStr = LTRIM(rtrim(LEFT(@InString,@pos-1)))
IF LEN(@TempStr)> 1
BEGIN
INSERT INTO @OutTable
SELECT @item_id,RTRIM(LTRIM(@TempStr))
END
SET @InString = SUBSTRING(@InString,@pos+1,LEN(@InString))
SET @pos = CHARINDEX(@DELIMITER,@InString,1)
END

INSERT INTO @OutTable
SELECT @item_id,RTRIM(LTRIM(@TempStr))

RETURN
END


The usage is:
SELECT item_id, UPPER(stringvalues) FROM splitintotable('Test1 test2 test3',' ',111)

This will return a table with three records (splitting on the spaces in the string). I hope this steers you in the right direction. I have added some 'scrubbing' to avoid any problems with SQL injections or when spewing the data to HTML.

Regards,

-R!