SQL Server: Split delimited string to table variable

In one of my projects I had to split a string which held a list of names delimited by the forwardslash '/' character into a proper table structure, so that the values could be used in a relational format.

I tried a few things including putting a bunch of PATINDEX type commands together, until I found a solution which works (it may not be particularly scalable). This also trims whitespace from either side of the delimiter in case they aren't evenly spaced:

---

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSplitDelimitedString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[fnSplitDelimitedString]
GO

CREATE FUNCTION fnSplitDelimitedString (@list nvarchar(MAX),@delimiter VARCHAR(1))
   RETURNS @tbl TABLE (name varchar(255) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(@delimiter , @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (name)
         VALUES (convert(varchar(255), ltrim(rtrim(substring(@list, @pos + 1, @valuelen)))))
      SELECT @pos = @nextpos
   END
   RETURN
END

SELECT name FROM fnSplitDelimitedString('Gerry Smith /   Tom Martin / Michael Green/Rohil Sen/Dominic Davidson/Richard Wilson','/') as Split 

---

You can pass in your own delimiter (comma, semicolon etc), and the function returns a table in this format which is a whole lot more useful:

Name
Gerry Smith
Tom Martin
Michael Green
Rohil Sen
Dominic Davidson
Richard Wilson

I found this solution on a blog post by Erland Sommarskog which has a lot more potential solutions. If the solution I posted doesn't work for you and you need something which will scale up, try reading his detailed write-up.

This solution was tried and tested on SQL Server 2005.