Another way to do it is by using the Tally table. Check out Jeff Moden's article http://www.sqlservercentral.com/articles/T-SQL/62867/.

Here's some example code (how i use it on our systems):

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfTransformStringToColumn] (

@StringVARCHAR(MAX),

@DelimiterVARCHAR(1)

)

RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)

AS

BEGIN

IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String

IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter

;WITH

cteTally AS

(

SELECTTOP (LEN(@String))

ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

FROMMaster.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)INSERT INTO @List (ValueColumn)

SELECTSUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value

FROMcteTally

WHEREN < LEN(@String)

AND SUBSTRING(@String,N,1) = @Delimiter

RETURN

END

GO