Split no Sql Server.sql

ALTER FUNCTION [dbo].[Split](@Input VARCHAR(MAX), @Var VARCHAR(MAX))
	RETURNS @Return TABLE (ID INT, Item VARCHAR(MAX))
BEGIN 
	DECLARE @ID INT 
	SET @ID = 0
	WHILE CHARINDEX(@Var, @Input) > 0 BEGIN
		SET @ID = @ID + 1
		INSERT INTO @Return VALUES (@ID, LTRIM(SUBSTRING(@Input, 0, CHARINDEX(@Var, @Input))))
		SET @Input = SUBSTRING(@Input, CHARINDEX(@Var, @Input) + 1, LEN(@Input) - 1)
	END 
	INSERT INTO @Return VALUES (@ID + 1, LTRIM(@Input))
	RETURN 
END
GO

-- Teste 01
SELECT * FROM Split('aaa aab aba abb', ' ')

-- Teste 02
SELECT * FROM Split('aaa,aab,aba,abb AAA', ',')