Extracting List Item from Delimited String

Performing string manipulations is not the best use of T-SQL. But there are a lot of operations that can be done using pure SQL. Here is one example. Given an input string that has list elements separated by delimiter, slice the list into individual element, and provided index parameter return the matching item based on index position in the list, NULL if the index was not found, or the entire string if the input index is 0. For this method we need utility table with numbers.

-- Create sample utility table with numbers
CREATE TABLE Numbers (
 n INT PRIMARY KEY)
 
INSERT INTO Numbers
SELECT number
FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 1 AND 100
 
GO
 
-- Extract function to perform the string split
CREATE FUNCTION dbo.Extract(
 @string VARCHAR(200),
 @delimiter CHAR(1),
 @idx INT)
RETURNS VARCHAR(200)
AS
BEGIN
 
RETURN
 CASE @idx
 WHEN 0 THEN @string
 ELSE
 (SELECT string
  FROM
      (SELECT SUBSTRING(@string, n,
              CHARINDEX( @delimiter, @string + @delimiter, n ) - n ),
              n + 1 - LEN(REPLACE(LEFT(@string, n), @delimiter, ''))
       FROM Numbers
       WHERE SUBSTRING(@delimiter + @string, n, 1) = @delimiter
         AND n < LEN(@string) + 1) AS T(string, idx)
  WHERE idx = @idx)
 END
 
END
GO
 
-- Test
DECLARE @foo VARCHAR(100)
SET @foo = 'Joe*Doe*123 Main Street'
 
SELECT dbo.Extract(@foo, '*', 2) -- returns 'Doe'
SELECT dbo.Extract(@foo, '*', 3) -- returns '123 Main Street'
SELECT dbo.Extract(@foo, '*', 0) -- returns entire string
SELECT dbo.Extract(@foo, '*', 9) -- returns null

If running SQL Server 2005 this can be accomplished much easier using CLR:

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public class CLRSplit
{
    [Microsoft.SqlServer.Server.SqlFunction()]
    public static string extract(string str, char delimiter, int index)
    {
        if (index == 0)
        { return str; }
        try
        {
            string[] list = new string[100];
            list = str.Split(new char[] { delimiter });
            return list[index - 1];
        }
        catch
        {
            return null;
        }
    }
}