Convert List To Array

This blog explains how to a convert a delimiter-separated list into a table. First of all we need to define a table valued  function that takes two parameters; the first parameter is a list and the second parameter is a delimiter character and returns a table. Then we use this function to generate a table from list. 

Function:

CREATE FUNCTION dbo.Fun_ListTOTable

(

@String_List VARCHAR(MAX),

@Delim_String CHAR

)

RETURNS

@Tab TABLE

(

item VARCHAR(MAX)

)

AS

BEGIN

DECLARE @item VARCHAR(MAX), @Pos INT

SET @String_List = @String_List+ @Delim_String;

WHILE LEN(@String_List)>0

BEGIN

SET @Pos = CHARINDEX(@Delim_String, @String_List, 1)

SET @item =SUBSTRING(@String_List,1,@Pos-1);

IF LEN(LTRIM(@item))>0

BEGIN

INSERT INTO @Tab (item)

VALUES (CAST(@item AS VARCHAR(MAX)))

END

SET @String_List = SUBSTRING(@String_List,@Pos+1,LEN(@String_List));

END

RETURN

END

GO

Example:

SELECT item AS Data

FROM dbo.Fun_ListTOTable('Pankaj#Sandeep#Neeraj#Sanjjev#Naru','#')

Result: