Mar 9, 2007

SPLIT function - TSQL

CREATE FUNCTION SPLIT (
@str_in VARCHAR(8000),
@separator VARCHAR(4) )
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN

DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr VARCHAR(8000)

SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0
BREAK

END
RETURN
END

kick it on DotNetKicks.com

3 comments:

Sergio said...

Exactly what I needed :)
Thank You

Todd said...

of note, the code doesn't work if the separator is more than 1 character because of the SET @tmpStr line in the loop is hard coded at 1, use len(@separator) instead. Also, might as well use nvarchar(max) instead of varchar(8000) for the variables unless you're working in a SQL 2000 environment.

Todd said...

oh, and when calculating @Occurances you do not want to divide by the length of the separator