SQL Server custom Split function

The following is a create script for the Split function, which should be run inside of your GPV database. This was created in SQL Server 2008, but is probably compatible with SQL Server 2005 & may even be compatible with SQL Server 2000.

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION <nowiki>[dbo].[Split]</nowiki>
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  ID Int Identity,
  myval nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(myval)
  select 
    r.value('.','varchar(100)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

GO