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)
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
(
@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 > 0BEGIN
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
RETURNEND
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
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
FROM dbo.udf_List2Table('1,First|2-nd|||3_Third|||||1+1+1+1','|')
GO
Result:
SELECT Item As sValuesFROM dbo.udf_List2Table('The Number 3 Is Prime',' ')
Result:
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
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.