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
© 2014 Syntax Warriors