Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008
Alternative
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)
21 Jan 2016CPOL 7.8K   2  
This is an alternative for "String splitting/tokenizing using T-SQL user defined function."

Introduction

This is another version of token splitting UDF for SQL Server. I needed to split "Token1=Value1|Token2=Value2|Token3=Value3" into components, and created a function based on the one proposed by Irfan Baig.

SQL
-- ----------------------------------------------------------------------------
-- Steve Mirson 1/21/2016 : a modified version of the function that 
-- splits Token1=Value1|Token2=Value2|Token3=Value3 type of string into components
-- Original version:
-- http://www.codeproject.com/Tips/154822/String-splitting-tokenizing-using-T-SQL-user-defin
-- Irfan Baig, 8 Feb 2011
-- ----------------------------------------------------------------------------
ALTER FUNCTION dbo.Split2 (@String varchar(8000), @TokenDelimiter varchar(10), @ValueDelimiter varchar(10)) 
RETURNS @Tokens TABLE (Token varchar(255), Value varchar(255)) 
AS 
BEGIN
   DECLARE @TokenValue varchar(1000), @p int
   WHILE @String <> '' BEGIN 
      -- Get the Token=Value part
      SET @p = CHARINDEX(@TokenDelimiter, @String)
      IF @p > 0 BEGIN
         SET @TokenValue = LTRIM(RTRIM(LEFT(@String, @p-1)))
         SET @String     = LTRIM(RTRIM(RIGHT(@String, LEN(@String) - @p - LEN(@TokenDelimiter) + 1)))
      END
      ELSE BEGIN 
         SET @TokenValue = @String
         SET @String     = ''
      END 

      -- Split into Token and Value
      SET @p = CHARINDEX(@ValueDelimiter, @TokenValue)
      IF @p > 0 BEGIN
         INSERT @Tokens (Token, Value) 
         SELECT
            Token = LTRIM(RTRIM(LEFT(@TokenValue, @p-1)))
           ,Value = LTRIM(RTRIM(RIGHT(@TokenValue, LEN(@TokenValue) - @p - LEN(@ValueDelimiter) + 1)))
      END
   END 
   RETURN
  
   -- Test:
   -- SELECT * FROM dbo.Split2 ('Token1=Value1|Token2=Value2|Token3=Value3', '|', '=') 
END
GO

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --