Sunday, December 16, 2018

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog