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_in		VARCHAR(1024),
	@separator	VARCHAR(4))
RETURNS @strtable TABLE (strval	VARCHAR(100))
AS
BEGIN

DECLARE	@str_total	VARCHAR(1024),
	@strpos		INT,
	@strlen_total	INT,
	@strlen_1	INT,
	@strlen_2	INT,
	@separator_len	INT

	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)

Share

Share

Rate

5 (1)