Get Nth Item From a Delimited String List

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