Tokenize your SQL!

If you ever went from SSIS to TSQL you might miss the cool tokenize function which gives you the stringpart that you are interessted in when you splitted a string in parts.

If so, then you might like my try to write down this function for SQL-Server:

CREATE FUNCTION dbo.TOKEN
	(
		@text nvarchar(max),
		@separator char,
		@resultpart int
	) 
RETURNS varchar(max)
AS
BEGIN
	DECLARE 
	@input varchar(max),
	@decider int = 1,
	@result varchar(max)
 
	DECLARE @resultset TABLE
	(
		id int NOT NULL identity(1,1), 
		value varchar(max)
	)
	BEGIN
		WHILE  @decider > 0
			BEGIN
				-- extrahieren des linken teils        
				insert into @resultset
				select substring(@text,0,charindex(@separator,@text))
				-- überschreiben des teststrings mit dem rest
				select @text = substring(@text,charindex(@separator,@text)+1,len(@text))
				-- einfügen des gefundenen datensatzes
				select @decider = len(substring(@text,0,charindex(@separator,@text)))
			END
		insert into @resultset
		select @text
	END
 
 
	SELECT @result = value FROM @resultset where id = @resultpart
	RETURN @result
END