CREATE FUNCTION dbo.split_part(@string VARCHAR(MAX),@deliminator VARCHAR(10))
RETURNS TABLE AS
RETURN (
WITH p AS (
SELECT SUBSTRING(@string, 1,
CASE charindex(@deliminator, @string)
WHEN 0
THEN len(@string)
ELSE charindex(@deliminator, @string) - 1
END
) AS parse_val,
charindex(@deliminator, @string) AS pos
WHERE @string IS NOT NULL
AND len(@string) > 0
UNION ALL
SELECT SUBSTRING(@string, pos + 1,
CASE charindex(',', @string, pos + 1)
WHEN 0
THEN len(@string) - pos
ELSE charindex(',', @string, pos + 1) - pos - 1
END ) AS parse_val,
charindex(',', @string, pos + 1) AS pos
FROM p
WHERE pos > 0
)
SELECT parse_val FROM p
)
//SQL/2005