Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 03/27/2009 at 10:22 AM	SQL\Code

Sometimes I run into a situation, where I need the ability to separate stings into single peaces and additionally I need that result as a table. The T-SQL Code for separating a string is no secret and to build this result in a return table is also quite easy.

The needed UDF function, called Split(), which do that for us gets a string to separate and the separator as input parameters and returns a table with the parts and positions as rows like the following figure shows :

SELECT * FROM Split('John Doe/John Miller/Doris Day', '/')

SELECT parts FROM [dbo].[Split](OrgaView.treepath,'/') [CEO] WHERE pos = 1 

DECLARE @tmpTable TABLE (
content VARCHAR(255)
)
INSERT INTO @tmpTable (content) VALUES ('John Doe/John Miller/Doris Day')
INSERT INTO @tmpTable (content) VALUES ('John Doe/John Miller/Richard Kimble')
INSERT INTO @tmpTable (content) VALUES ('John Doe/Jack Frost/Tony Blaire')
INSERT INTO @tmpTable (content) VALUES ('John Doe/Jack Frost/John Sinclair')


SELECT * FROM @tmpTable
SELECT 
SELECT Parts FROM split(content, '/') WHERE pos = 1) [CEO]
,(SELECT Parts FROM split(content, '/') WHERE pos = 2) [TL]
,(SELECT Parts FROM split(content, '/') WHERE pos = 3) [TL]
FROM @tmpTable