CreateFunctionSplit.sql

/*
borrowed from : http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Use this function to split values in a comma delimited list into a TABLE variable
with one column named 'val', and a row for each value in your list.

usage  : SELECT * FROM dbo.Split(N'a,b,c,d,e', ',')

-- 
USE SmartEarth
DROP FUNCTION Split
*/

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  val nvarchar(max)
)
AS
BEGIN
	DECLARE @xml XML
	
	-- convert delimited list into XML object
	SET @xml = N'<root><r>' + REPLACE( @delimited, @delimiter, '</r><r>') + '</r></root>'
	
	-- insert XML data into TABLE
	INSERT INTO	@t(val)
	SELECT	LTRIM( RTRIM( r.value('.','varchar(5)') ) )as item
	FROM	@xml.nodes('//root/r') as records(r)

	-- RETURN @t
	RETURN
END
GO