T-SQL: Splitting strings into multiple rows

Functionality such as Split() is for the most of us something we take for granted in whatever programming language we are using. There is however no Split method in MSSQL. So when I was faced with the problem of splitting strings into multiple rows it got a bit tricky.

I ended up with the function below which will take two arguments, the string to work with, and the separator to split on.

CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(n, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT n,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

Here is an example of what this code will do:

SELECT * FROM  dbo.CustomSplit('x','aaaaaaxbbbbbbbbbxcccccccc')

Now my problem had one final twist. I had multilpe rows with multiple columns of which one column contained data that I needed to split into many rows. This meant that I needed to make sure the other values that were on the row were also copied to all the new rows.

Basically I needed to turn the rows on the left into the rows on the right in the image below.

To do this I used the Split function above together with the CROSS APPLY command from T-SQL.

In order to demonstrate this I created this self contained example that you should be able to copy and paste into SQL Studio.

-- set up a split function
GO
CREATE FUNCTION [dbo].[TempSplit] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

-- set up some test data
DECLARE @personroles TABLE
    (
        idperson INT,
        rawroleoptions NVARCHAR(MAX)
    )

INSERT INTO @personroles VALUES (1, ';1;2;3;')
INSERT INTO @personroles VALUES (2, ';4;5;6;')
INSERT INTO @personroles VALUES (3, ';7;')

SELECT * FROM @personroles

-- the actual work --
;WITH data AS
(
    SELECT
        p.idperson,
        p.rawroleoptions

    FROM @personroles p
)
SELECT * FROM data r
CROSS APPLY
    (SELECT s AS [ExtractedValue] FROM dbo.TempSplit(';',r.rawroleoptions) WHERE LEN(s)>0) d

-- clean up --
GO
DROP FUNCTION dbo.TempSplit
GO