Technical Article

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

Rate

2.56 (9)

Share

Share

Rate

2.56 (9)