Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
8 Feb 2011CPOL 26.9K   5   2
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.

SQL
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:

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


Expected output:
Token1
Token2
Token3
Token4
Token5

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Pakistan Pakistan
This member doesn't quite have enough reputation to be able to display their biography and homepage.

Comments and Discussions

 
GeneralIf you face some performance related issues then please read... Pin
Irfan Baig7-Feb-11 18:18
Irfan Baig7-Feb-11 18:18 
GeneralRe: 1. Your formatting is off. 2. All comments will get deleted... Pin
Indivara7-Feb-11 19:52
professionalIndivara7-Feb-11 19:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.