TSQL: Split one column with semicolon separated values into multiple rows

t-sql

Solution

While writing this post I figured out an easy way to do this using cross apply and a custom split function. So instead of just asking the question I'll post how I solved it using this self contained SQL too ;)

-- 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;')

-- 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

After running the above, this is what SQL outputs.

Problem

Trying to migrate from an old database to a new one in which the way data is stored is a bit different. In one specific case I have a column with semicolon separated values that I would like to separate into multiple rows. Here is an example: ``` SELECT p.idperson, p.roleperson FROM person p ``` The TSQL above generates the following output ``` idperson roleperson 1001 ;214401; 1002 ;214201;214401; 1003 ;212101; ``` I would like to convert this to: ``` idperson roleperson 1001 214401 1002 214401 1002 214201 1003 212101 ``` That is, I want to split the row with multiple values into two rows. Is this possible without creating cursors or loops?

Original source