SQL split string by a delimiter to create new rows

I’ve just spent a reasonable amount of time digging through the internet to work out how to take a dataset containing a delimited string and then split the string to create new records. For example you could have a table called Users:

CountryUser
UKBob|Fred|Bill
USAMike|Lou|Carol|Sam

What you want to do is split the string by the delimiter and create new rows:

CountryUser
UKBob
UKFred
UKBill
USAMike
USALou
USACarol
USASam

Apparently there are many different ways to create this but the neatest I came across was to use a combination of Common Table Expression (CTE), Table Function and Cross Apply.

First create the table function, which contains the CTE, which is where the string split happens:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(1000))

RETURNS table

AS

RETURN

(

WITH Pieces AS (

SELECT 1 as pn, 1 as start, CHARINDEX(@sep, @s) as stop

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 1000 END) AS s

FROM Pieces

)

To call this function use a Cross Apply in a select statement to join to the original table, populating the parameters defined in the table function with the delimiter character and the string to split:

SELECT Country

,s

FROM Users

CROSS APPLY dbo.Split (‘|’,UserName )

Credit where credit is due, this solution was taken from these sites:

Function and CTE: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Calling the function using Cross Apply: http://stackoverflow.com/questions/335886/how-to-pass-parameters-to-table-valued-function

2015-02-04T21:07:40+00:00 February 4th, 2015|SQL|

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.