String splitting/tokenizing using T-SQL user defined function.

We often have need of string splitting in applications. I had need of one in some T-SQL development I am currently doing for a friend. After development and testing, I’ve decided this should be available for all dudes over the net who need this kind of functionality in their applications. So I am uploading function’s source code on my favorite site.

Simply copy and paste the following 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 and you can use it easily.

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

GO

To execute the function using the following example:

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

Expected output:

Token1
Token2
Token3
Token4
Token5