SQL split string by a delimiter to create new rows

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

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

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