Here's my test version of a function using the technique discussed in Jeffs article that was already mentioned. You'll probably want to really test it out under your required conditions and make sure I don't mess you up because I'm not Jeff 🙂

Ran at least once in sql 2000 LOL

CREATE FUNCTION dbo.FN_LIST2TAB

(

@LIST_IN VARCHAR(1000),

@DELIM_IN VARCHAR(1)

)

RETURNS TABLE

RETURN

SELECT

SUBSTRING(@DELIM_IN + @LIST_IN + @DELIM_IN, TALLY1 + 1,

CHARINDEX(@DELIM_IN,@DELIM_IN + @LIST_IN + @DELIM_IN,TALLY1 + 1) - TALLY1 - 1) ACTUAL_COLUMN

FROM

(

SELECT TALLY1 FROM

(

SELECT D.VAL1 * 1000 + C.VAL1 * 100 + B.VAL1 * 10 + A.VAL1 TALLY1 FROM

(

SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION

SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1

) A

CROSS JOIN

(

SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION

SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1

) B

CROSS JOIN

(

SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION

SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1

) C

CROSS JOIN

(

SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION

SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1

) D

) TALLYTAB

) LISTBUILDER

WHERE SUBSTRING(@DELIM_IN + @LIST_IN + @DELIM_IN,TALLY1,1) = @DELIM_IN AND TALLY1 < LEN(@DELIM_IN + @LIST_IN + @DELIM_IN)