String tokenizing / splitting

Simply copy and paste the Split() function code in your T-SQL code editor, to select appropriate DB from database combo box and then press F5 key to create user defined Split() function.

Now you can use it as follows:
1. The first parameter of function is string that you need to split.
2. And second parameter of function is delimiter.

SELECT * FROM dbo.Split ('Token1;Token2;Token3;Token4;Token5',';')

The Expected output will be as follow:
Token1
Token2
Token3
Token4
Token5



CREATE FUNCTION dbo.Split
(
@String VARCHAR(8000),
@Delimiter NVARCHAR(1)
)
RETURNS @Tokens table (Token NVARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END