Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - Get Nth Item From a Delimited String List

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        17 Jul 2012

Description

This is based around the same code as the very fast string splitter function and will return the Nth item from a delimited list supplied as a single string
 
ie  the 4th item from 'this, is , a ,comma, delimited, string' would be 'comma'

Code

Function:


DROP FUNCTION dbo.udf_GetNthItemFromDelimitedStringList
GO

CREATE FUNCTION dbo.udf_GetNthItemFromDelimitedStringList
(
  
@List       VARCHAR(8000),      -- the delimited list
  
@Delimiter  CHAR(1),            -- the delimiter
  
@N          INT                 -- the list  item number required
)
RETURNS VARCHAR(8000)
AS


/**********************************************************
Purpose:   Returns the Nth item from a delimited string list.

Author:        ChillyDBA
History        17 Jul 2012 - Initial Issue

***********************************************************/

BEGIN
   DECLARE
@Result  VARCHAR(8000)

  
-- remove and CR/LF characters
  
SET @List = REPLACE(REPLACE(@List,CHAR(10),''),CHAR(13),'')

  
SELECT @Result = x.value
  
FROM
  
(
      
SELECT
          
ROW_NUMBER() OVER (ORDER BY i ASC) AS RowNumber,
          
LTRIM(SUBSTRING(@Delimiter  + @List + @Delimiter , w.i + 1, CHARINDEX(@Delimiter , @Delimiter  + @List + @Delimiter , w.i + 1) - w.i - 1)) value
      
FROM
      
(
          
SELECT TOP 100 PERCENT
              
v0.n + v1.n + v2.n + v3.n i
          
FROM
          
(
              
SELECT 0 n    UNION ALL    SELECT 1 UNION ALL
              
SELECT 2 UNION ALL    SELECT 3 UNION ALL
              
SELECT 4 UNION ALL    SELECT 5 UNION ALL
              
SELECT 6 UNION ALL    SELECT 7 UNION
               SELECT
8 UNION ALL    SELECT 9 UNION ALL
              
SELECT 10    UNION ALL    SELECT 11    UNION ALL
              
SELECT 12    UNION ALL    SELECT 13    UNION ALL
              
SELECT 14    UNION ALL    SELECT 15
          
) AS v0,
           (
              
SELECT 0 n    UNION ALL    SELECT 16    UNION ALL
              
SELECT 32    UNION ALL    SELECT 48    UNION ALL
              
SELECT 64    UNION ALL    SELECT 80    UNION ALL
              
SELECT 96    UNION ALL    SELECT 112   UNION
               SELECT
128   UNION ALL    SELECT 144   UNION ALL
              
SELECT 160   UNION ALL    SELECT 176   UNION ALL
              
SELECT 192   UNION ALL    SELECT 208   UNION ALL
              
SELECT 224   UNION ALL    SELECT 240
          
) AS v1,
           (
              
SELECT 0 n    UNION ALL    SELECT 256   UNION ALL
              
SELECT 512   UNION ALL    SELECT 768   UNION ALL
              
SELECT 1024 UNION ALL  SELECT 1280 UNION ALL
              
SELECT 1536 UNION ALL  SELECT 1792 UNION
               SELECT
2048 UNION ALL  SELECT 2304 UNION ALL
              
SELECT 2560 UNION ALL  SELECT 2816 UNION ALL
              
SELECT 3072 UNION ALL  SELECT 3328 UNION ALL
              
SELECT 3584 UNION ALL  SELECT 3840
          
) AS v2,
           (
              
SELECT 0 n    UNION ALL    SELECT 4096
          
) AS v3 ORDER BY i
      
) w
      
WHERE w.i = CHARINDEX(@Delimiter , @Delimiter  + @List + @Delimiter , w.i)
       AND
w.i < LEN(@Delimiter  + @List)
   )
AS x
  
WHERE RowNumber = @N
  
  
RETURN @Result
END


Test Code:

 

SELECT  dbo.udf_GetNthItemFromDelimitedStringList('this, is,  a, comma,delimited,  list', ',', 3)

SELECT  dbo.udf_GetNthItemFromDelimitedStringList('the^quick^brown^carat^jumped^over^the^lazy^dog', '^', 4)


ċ
udf_GetNthItemInDelimitedStringList.sql
(3k)
Andy Hughes,
Jul 17, 2012, 12:39 PM
Comments