Thursday, March 26, 2009

SQL Server 2005 : Split Function

CREATE FUNCTION dbo.Splitter(@strList VARCHAR(8000),@strSeperator VARCHAR(10))
Returns @tblSplit TABLE (splitValue VARCHAR(255))
AS
BEGIN
DECLARE @tmp_FnCommaSplitter TABLE (splitValue VARCHAR(255))
DECLARE @strTmp VARCHAR(5000)
DECLARE @pos INT
DECLARE @lenSeperator INT
SELECT @lenSeperator = LEN(@strSeperator)
SELECT @strTmp = @strList
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
WHILE (@pos <> 0)
BEGIN
IF SUBSTRING(@strTmp,1,@pos-1) <> ''
INSERT @tmp_FnCommaSplitter VALUES (SUBSTRING(@strTmp,1,@pos-1))

SELECT @strTmp = SUBSTRING(@strTmp,@pos+@lenSeperator,5000)
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
END
IF @strTmp <> ''
INSERT @tmp_FnCommaSplitter VALUES (LTRIM(RTRIM(@strTmp)))
INSERT @tblSplit SELECT * FROM @tmp_FnCommaSplitter
RETURN
END

No comments: