TSQL String Tokenizer Function for SQL Server

NOTE: If your database is 2016 or newer, you can use the new STRING_SPLIT function. See an example at the end of this post.

String tokenizer for SQL Server 2008.

We often have need of string tokenizers in applications. I had need of one in some TSQL development I am did for a customer. I had previously written one for Oracle so I decided to re-write it for SQL Server 2008 (I believe it should work in 2005).

The main limitations with the conversion are the lack of a hierarchical “CONNECT BY LEVEL” clause and the lack of a LEAD() function for SQL Server’s limited implementation of analytics. Also, the function is dependent on my generate_series() function as described in this article.

Still, with a little perseverance I came up with the following working implementation.

USE [GISDB]  -- You need to change this if you use this function.
GO
-- Create Tokenizer Function
--
CREATE FUNCTION [dbo].[Tokenizer]
(
  @p_string     VarChar(MAX),
  @p_separators VarChar(254)
)
Returns @tokens TABLE 
(
  id        int,
  token     varchar(MAX),
  separator varchar(MAX)
)
As
/****f* TOOLS/Tokenizer (2008)
 *  NAME
 *    Tokenizer - Splits any string into tokens and separators.
 *  SYNOPSIS
 *    Function Tokenizer (
 *       @p_string     varchar(max),
 *       @p_separators varchar(254)
 *     )
 *     Returns @tokens TABLE 
 *     (
 *       id        int,
 *       token     varchar(MAX),
 *       separator varchar(MAX)
 *     ) 
 *  EXAMPLE
 *    SELECT t.id, t.token, t.separator
 *      FROM [dbo].[TOKENIZER]('LINESTRING(0 0,1 1)',' ,()') as t
 *    GO
 *    id token       separator
 *    -- ---------- ---------
 *     1 LINESTRING (
 *     2 0          NULL
 *     3 0          ,
 *     4 1          NULL 
 *     5 1          )
 *  DESCRIPTION
 *    Supplied a string and a list of separators this function returns resultant tokens as a table collection.
 *    Function returns both the token and the separator.
 *    Returned table collection contains a unique identifier to ensure tokens and separators are always correctly ordered.
 *  INPUTS
 *    @p_string     (varchar max) - Any non-null string.
 *    @p_separators (varchar 254) - List of separators eg '(),'
 *  RESULT
 *    Table (Array) of Integers
 *      id        (int)         - Unique identifier for each row starting with first token/separator found.
 *      token     (varchar MAX) - Token between separators
 *      separator (varchar MAX) - Separator between tokens.
 *  AUTHOR
 *    Simon Greener
 *  HISTORY
 *    Pawel Barut, http://pbarut.blogspot.com/2007/03/yet-another-tokenizer-in-oracle.html
 *    Simon Greener -  Aug 2008 - Converted to SQL Server 2008
 *    Simon Greener -  Aug 2012 - Added extra return variables.
 *    Simon Greener - June 2020 - Converted to Inline Table-Valued Function from Multi-Statement Table-Valued Function
 *  COPYRIGHT
 *    (c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener
******/
BEGIN
WITH MyCTE AS (
  SELECT c.beg, 
         c.sep, 
         ROW_NUMBER() OVER(ORDER BY c.beg ASC) as rid
    FROM (SELECT b.beg, c.sep
            FROM (SELECT a.beg 
                    FROM (SELECT c.IntValue as beg
                            FROM [dbo].[generate_series](1,DATALENGTH(@p_string),1) c 
                          ) a
                  ) b,
                  (SELECT SUBSTRING(@p_separators,d.IntValue,1) as sep
                      FROM [dbo].[generate_series](1,DATALENGTH(@p_separators),1) d
                    ) c
            WHERE CHARINDEX(c.sep,SUBSTRING(@p_string,b.beg,1)) > 0
          UNION ALL 
          SELECT 0 as beg, CAST(NULL AS varchar) as sep
         ) c
)
INSERT INTO @tokens
SELECT ROW_NUMBER() OVER (ORDER BY a.rid ASC) as Id, 
       CASE WHEN DATALENGTH(a.token) = 0 THEN NULL ELSE a.token END as token, 
       a.separator
  FROM (SELECT d.rid,
                SUBSTRING(@p_string, 
                         (d.beg + 1), 
                         (d.end_p - d.beg - 1) ) token,
               d.separator
          FROM (SELECT BASE.rid,
                       BASE.beg,
                       LEAD.beg end_p,
                       LEAD.sep separator
                 FROM MyCTE BASE LEFT JOIN MyCTE LEAD ON BASE.rid = LEAD.rid-1
               ) d
           WHERE d.end_p IS NOT NULL
       ) as a;
RETURN;
END;
GO

Here are my, simple, tests.

 SELECT distinct t.token
   FROM dbo.Tokenizer('LineString:MultiLineString:MultiPoint:MultiPolygon:Point:Point:LineString:Polygon:Polygon',':') as t;

Result.

 SELECT t.token
   FROM dbo.tokenizer('The rain in spain, stays mainly on the plain.!',' ,.!') t;

Result.

Now, if you want to collect them back into a single string, here’s an example of what you can do.

 SELECT (STUFF((SELECT DISTINCT ':' + a.gtype
                  FROM (SELECT distinct t.token as gtype
                          FROM dbo.Tokenizer('LineString:MultiLineString:MultiPoint:MultiPolygon:Point:Point:LineString:Polygon:Polygon',':') as t
                        ) a
                 ORDER BY ':' + a.gtype
                 FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')
         ) as GeometryTypes;

Result.

Upgraded Version for SQL Server 2012 and aboce

SQL Server 2012 and above provide the LAG/LEAD analytic functions that were missing from 2008. The above 2008 Tokenizer function can be re-written as follows.

 -- Connect to database holding generate_series
 --
 USE [GISDB] -- Change to your database
 GO
 -- Drop function if exists
 --
 IF EXISTS (SELECT *
              FROM dbo.sysobjects 
             WHERE id = object_id (N'[dbo].[Tokenizer]') 
               AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
 DROP FUNCTION [dbo].[Tokenizer]
 GO
 --
 -- Now let's create it
 --
CREATE FUNCTION [dbo].[Tokenizer]
(
  @p_string     VarChar(MAX),
  @p_separators VarChar(254)
)
Returns @tokens TABLE 
(
  id        int,
  token     varchar(MAX),
  separator varchar(MAX)
)
As
/****f* TOOLS/Tokenizer (2012)
 *  NAME
 *    Tokenizer - Splits any string into tokens and separators.
 *  SYNOPSIS
 *    Function Tokenizer (
 *       @p_string     varchar(max),
 *       @p_separators varchar(254)
 *     )
 *     Returns @tokens TABLE 
 *     (
 *       id        int,
 *       token     varchar(MAX),
 *       separator varchar(MAX)
 *     ) 
 *  EXAMPLE
 *
 *    SELECT t.id, t.token, t.separator
 *      FROM [dbo].[TOKENIZER]('LINESTRING(0 0,1 1)',' ,()') as t
 *    GO
 *    id token       separator
 *    -- ---------- ---------
 *     1 LINESTRING (
 *     2 0          NULL
 *     3 0          ,
 *     4 1          NULL 
 *     5 1          )
 *
 *  DESCRIPTION
 *    Supplied a string and a list of separators this function returns resultant tokens as a table collection.
 *    Function returns both the token and the separator.
 *    Returned table collection contains a unique identifier to ensure tokens and separators are always correctly ordered.
 *  INPUTS
 *    @p_string     (varchar max) - Any non-null string.
 *    @p_separators (varchar 254) - List of separators eg '(),'
 *  RESULT
 *    Table (Array) of Integers
 *      id        (int)         - Unique identifier for each row starting with first token/separator found.
 *      token     (varchar MAX) - Token between separators
 *      separator (varchar MAX) - Separator between tokens.
 *  AUTHOR
 *    Simon Greener
 *  HISTORY
 *    Simon Greener - Aug 2012 - Converted to SQL Server 2012 (Uses new Lag/Lead function and returns separators)
 *  COPYRIGHT
 *    (c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener
******/
BEGIN
WITH MyCTE AS (
  SELECT c.beg, 
         c.sep, 
         ROW_NUMBER() OVER(ORDER BY c.beg ASC) as rid
    FROM (SELECT b.beg, c.sep
            FROM (SELECT a.beg 
                    FROM (SELECT c.IntValue as beg
                            FROM [dbo].[generate_series](1,DATALENGTH(@p_string),1) c 
                          ) a
                  ) b,
                  (SELECT SUBSTRING(@p_separators,d.IntValue,1) as sep
                      FROM [dbo].[generate_series](1,DATALENGTH(@p_separators),1) d
                    ) c
            WHERE CHARINDEX(c.sep,SUBSTRING(@p_string,b.beg,1)) > 0
          UNION ALL 
          SELECT 0 as beg, @p_string as sep
         ) c
)
INSERT INTO @tokens
SELECT Row_Number() OVER (ORDER BY a.rid ASC) as id, 
       Case When DATALENGTH(a.token) = 0 Then null Else a.token End as token, 
       a.separator
  FROM (SELECT d.rid,
               SubString(@p_string, (d.beg + 1), (Lead(d.beg,1) OVER (ORDER BY d.rid ASC) - d.beg - 1) ) as token,
               Lead(d.sep,1) OVER (ORDER BY d.rid asc) as separator
          FROM MyCTE d 
       ) as a
 WHERE DATALENGTH(a.token) <> 0 or DATALENGTH(a.separator) <> 0;
RETURN;
END;
GO

Testing

 select distinct t.token
   from dbo.Tokenizer('LineString:MultiLineString:MultiPoint:MultiPolygon:Point:Point:LineString:Polygon:Polygon',':') as t;

Results

The classic “Rain in Spain…”.

 SELECT t.*
   FROM dbo.tokenizer('The rain in spain, stays mainly on the plain.!',' ,.!') t;

Results

Now, let’s process a POLYGON WKT.

 SELECT t.id, t.token, t.separator
   FROM dbo.tokenizer('POLYGON((2300 400, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400), (2300 1000, 2400 900, 2200 900, 2300 1000))',' ,()') as t;

Results

This time don’t include the space as a separator.

 SELECT t.id, t.token, t.separator
   FROM dbo.tokenizer('POLYGON((2300 400, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400), (2300 1000, 2400 900, 2200 900, 2300 1000))',',()') as t;

Results

SQL Server 2016+ STRING_SPLIT

The STRING_SPLIT function released for SQL Server 2016 can be used instead of my Tokenizer function. The main difference is that the STRING_SPLT function returns only the tokens (or an additional ordinal identifier field) whereas the Tokenizer functions return a token id (ordinal), the token, and the separator between the tokens.

select distinct t.value
  from STRING_SPLIT('LineString:MultiLineString:MultiPoint:MultiPolygon:Point:Point:LineString:Polygon:Polygon',':') as t;

-- Result
--
value
LineString
MultiLineString
MultiPoint
MultiPolygon
Point
Polygon

-- Now return the ordinal value as an id.
--
select t.ordinal as id, t.value as token
  from STRING_SPLIT('LineString:MultiLineString:MultiPoint:MultiPolygon:Point:Point:LineString:Polygon:Polygon',':',1) as t;

-- Result
--
id	token
1	LineString
2	MultiLineString
3	MultiPoint
4	MultiPolygon
5	Point
6	Point
7	LineString
8	Polygon
9	Polygon

I hope that someone out there finds this post useful.