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;

        }

    }

}

1 reply
  1. Mohammad Salimabadi
    Mohammad Salimabadi says:

    Hi Plamen,
    I have created a set-based solution without CHARINDEX function, but number of scan is two.

    DECLARE @s VARCHAR(620)='item_01,item_02,item_03';
    SET @s = ','+ @s + ',';

    ;WITH C AS
    (SELECT n, ROW_NUMBER()OVER(ORDER BY n) AS rn
    FROM Numbs
    WHERE SUBSTRING(@s, n, 1) = ','
    AND n <= LEN(@s))
    SELECT SUBSTRING(@s, A.n + 1, B.n – 1 – A.n) AS item
    FROM C A
    INNER JOIN C B
    ON A.rn + 1 = B.rn
    AND B.n-A.n > 1;

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *