Click here to Skip to main content
Click here to Skip to main content
Alternative Tip
Go to top

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

, 14 Feb 2011
Rate this:
Please Sign up or sign in to vote.
There is a little inconsistence in this function, because the @Tokens Elements and the @Delimiter parameter are of data type NVARCHAR (=Unicode) while the @String parameter is of data type VARCHAR (=ANSI). If the @String parameter can be Unicode, it must be declared as NVARCHAR. If the @String...
There is a little inconsistence in this function, because the @Tokens Elements and the @Delimiter parameter are of data type NVARCHAR (=Unicode) while the @String parameter is of data type VARCHAR (=ANSI). If the @String parameter can be Unicode, it must be declared as NVARCHAR. If the @String parameter never can be Unicode, there is no need to declare the @Delimiter parameter or the Tokens as NVARCHAR.
It is posssible that the @Delimiter parameter can content more than one character. I believe, a possible length of 10 characters is enough. The function can look like this:
 
CREATE FUNCTION dbo.Split (@String NVARCHAR(8000), @Delimiter NVARCHAR(10)) 
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
 
A remaining TODO is to add the ability to handle brackets or quotation marks.

License

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

Share

About the Author

Hajo Messner

Germany Germany
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 14 Feb 2011
Article Copyright 2011 by Hajo Messner
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid