@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
3 comments:
Exactly what I needed :)
Thank You
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.
oh, and when calculating @Occurances you do not want to divide by the length of the separator
Post a Comment