Technical Article

SPLIT function

,

This is a port of the SPLIT function from Perl (or VBScript). It works the same way: pass a string and
a separator, up to 4 characters long (you can change this),
and the function returns a table with the elements.
This version trims leading and trailling spaces of the
elements, just for convenience.

Example:

SELECT    strval
FROM    master.dbo.SPLIT('a and b', 'and')

returns:

strval                                                                                              
------
a
b

CREATE FUNCTION dbo.SPLIT
(@str_inVARCHAR(1024),
@separatorVARCHAR(4))
RETURNS @strtable TABLE (strvalVARCHAR(100))
AS
BEGIN

DECLARE@str_totalVARCHAR(1024),
@strposINT,
@strlen_totalINT,
@strlen_1INT,
@strlen_2INT,
@separator_lenINT

SELECT@str_total = @str_in + @separator,
@strpos = 1,
@strlen_1 = CHARINDEX(@separator, @str_total, @strpos) - 1,
@strlen_2 = 0,
@separator_len = LEN(@separator)

WHILE (CHARINDEX(@separator, @str_total, @strpos) > 0)
BEGIN
INSERT INTO @strtable
VALUES (SUBSTRING(@str_total, @strpos, @strlen_1))

SELECT@strlen_2 = @strlen_1

SELECT@strpos = @strpos + @strlen_2 + @separator_len + 1
SELECT@strlen_1 = CHARINDEX(@separator, @str_total, @strpos) - @strpos
END

RETURN 

END

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating