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