How to Convert a delimited List of Values from a Row into Multiple Rows based on Delimiter in SQL Server

User Defined Function to Convert a delimited List of Values from a Row into Multiple Rows based on Delimiter in SQL Server

Suppose we have the delimited(eg. comma, space, pipe) List of values as follows...

Select '111,222,333,444,555' as csvDelim_Num

Select '111,Two,333,Fourth,555' as csvDelim_Text

Select 'The Number 3 Is Prime' as spaceDelim_Text

Select '1,First|2-nd|||3_Third|||||1+1+1+1' as pipeDelim_Text

To Split a different kind of delimited List of values, We will created a User defined Function using the combination of multiple String Functions as follows..

CREATE FUNCTION [dbo].[udf_List2Table]
(
@List VARCHAR(8000),
@Delim VARCHAR(5)
)
RETURNS
 @ParsedList TABLE
  (
  Item VARCHAR(4000)
  )
AS
BEGIN
  Declare @Item Varchar(4000), @Pos INT
  Set @List = LTRIM(RTRIM(@List))+ @Delim
  Set @Pos = CHARINDEX(@Delim, @List, 1)

 IF @Delim=' '
  BEGIN
   Set @Delim='='
   Set @List=REPLACE(@List,' ','=')
   Set @List = LTRIM(RTRIM(@List))+ @Delim
   Set @Pos = CHARINDEX(@Delim, @List, 1)

  END 
  

 WHILE @Pos > 0
  BEGIN
   SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)))
   IF @Item <> ''
    BEGIN
     Insert Into @ParsedList (Item)
     Values (CAST(@Item As Varchar(4000)))
    END
   SET @List = RIGHT(@List, LEN(@List) - @Pos)
   SET @Pos = CHARINDEX(@Delim, @List, 1)

  END

RETURN
END

GO

Now we can Pass the delimited List of values into the above function to split them based on the specified delimiter.

SELECT CAST(Item AS INT) As sValues
FROM dbo.udf_List2Table('111,222,333,444,555',',')
GO

Result:

SELECT Item As sValues

FROM dbo.udf_List2Table('111,Two,333,Fourth,555',',')

Result:

SELECT Item AS sValues
FROM dbo.udf_List2Table('1,First|2-nd|||3_Third|||||1+1+1+1','|')
GO

Result:

SELECT Item As sValues
FROM dbo.udf_List2Table('The Number 3 Is Prime',' ')
Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------