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))
WITH Pieces AS (
SELECT 1 as pn, 1 as start, CHARINDEX(@sep, @s) as stop
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
WHERE stop > 0
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 1000 END) AS s
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.
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