string splitter with t-sql (sql server and sybase)

The following function for sql server should give you an idea of string splitting into a table without looping. The idea should work with both sql server and sybase.

CREATE FUNCTION [dbo].[SplitString](@arr AS VARCHAR(8000), @sep AS CHAR(1))

RETURNS TABLE

AS

RETURN

SELECT

(number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,

LTRIM(RTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number))) AS element

FROM (select number from master..spt_values where type='p') as Numbers

WHERE number <= LEN(@arr) + 1

AND SUBSTRING(@sep + @arr, number, 1) = @sep;