Ever need to split a delimited string in SQL, creating new records? For example you could have a table called Users:

Country User
UK Bob|Fred|Bill
USA Mike|Lou|Carol|Sam

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

Country User
UK Bob
UK Fred
UK Bill
USA Mike
USA Lou
USA Carol
USA Sam

Apparently there are many different ways to create this. The neatest I know is 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 Cross Apply in a select statement, joining to the original table. This populates 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