SQL Developers Toolbox – Splitter Function

Article
1/17/2024

Introduction

There are many case uses for splitter functions. Maybe you have old legacy data in your database which has multiple values stored in a single column. Perhaps you’re sanitizing data as it comes into your database. Either way, a good, robust and well performing split technique is a must for us all.

Demo Data

Let’s setup an example using some made up data.

DECLARE @table TABLE  (ID INT, delimitedValues VARCHAR(MAX))
INSERT INTO  @table  (ID, delimitedValues) VALUES
(1, '123,456,789,101112'),(2,'the quick brown fox, jumps, over the lazy, dog'),(3,'that''s, great, it, starts, with, an, earthquake')
 
SELECT *
  FROM @table

There we have it, three rows of data that’s delimited by commas.

Now we need a splitter function to pull it apart. I’m going to list my most up to date incarnation of my splitter function here. This one has not had as much testing done on it, but it should perform in much the same way as the previous one.

splitterMkII

CREATE FUNCTION  dbo.splitterMkII(@string VARCHAR(MAX), @delim VARCHAR(10))
RETURNS @result TABLE (id INT IDENTITY, value VARCHAR(MAX))
AS
BEGIN
 WHILE CHARINDEX(@delim,@string) > 0
  BEGIN
   INSERT INTO  @result (value) VALUES (LEFT(@string,CHARINDEX(@delim,@string)-1))
   SET @string = RIGHT(@string,LEN(@string)-(CHARINDEX(@delim,@string)+(LEN(@delim)-1)))
  END
   INSERT INTO  @result (value) VALUES (@string)
 
RETURN
END
GO

The difference between this one and the previous version is just the variable length delimiter. MkII allows for any length of delimiter, the previous only allowed for a single character. For reference purposes, here’s the old one.

Splitter

CREATE FUNCTION  dbo.splitter(@string VARCHAR(MAX), @delim CHAR(1))
RETURNS @result TABLE (id INT IDENTITY, value VARCHAR(MAX))
AS
BEGIN
 WHILE CHARINDEX(@delim,@string) > 0
  BEGIN
   INSERT INTO  @result (value) VALUES (LEFT(@string,CHARINDEX(@delim,@string)-1))
   SET @string = RIGHT(@string,LEN(@string)-CHARINDEX(@delim,@string))
  END
   INSERT INTO  @result (value) VALUES (@string)
 
RETURN
END
GO

Both functions accept a string (to MAX) and a delimiter to split that string by:

SELECT *
 FROM dbo.splitterMkII('string1======string2======string3','======')

They return a table of IDs and values. This allows you to select particular splits, should the need arise:

SELECT *
 FROM dbo.splitterMkII('string1======string2======string3','======')
 WHERE ID IN  (2,3)

Putting it to use

You can use the function with a pivot to turn those rows into columns

DECLARE @table TABLE  (ID INT, delimitedValues VARCHAR(MAX))
INSERT INTO  @table  (ID, delimitedValues) VALUES
(1, '123,456,789,101112'),(2,'the quick brown fox, jumps, over the lazy, dog'),(3,'that''s, great, it, starts, with, an, earthquake')
 
SELECT *
  FROM @table t
    CROSS APPLY dbo.splitterMkII(delimitedValues,',') s
    PIVOT (
           MAX(value) FOR  s.ID IN ([1],[2],[3],[4],[5],[6],[7],[8])
       ) p

And there we have it. Delimited data into columns.

Share via