SQL SERVER – Remove Duplicate Entry from Comma Delimited String – UDF

I love reader’s contribution this blog as that brings variety in articles. I encourage my readers to provide their contribution and I will publish then with their name.

Blog Reader Ashish Jain has posted very simple script which will remove duplicate entry from comma delimited string. User Defined Function has very simple logic behind it. It takes comma delimited string and then converts it to table and runs DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used.

I have modified original contribution from Ashish so now it completely covers the subject intended to cover. I would suggest that this UDF should be kept handy to perform this tedious task easily.

Solarwinds

CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR
(MAX)
AS
BEGIN
DECLARE
@ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET
@list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT
@rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
GO

SQL SERVER - Remove Duplicate Entry from Comma Delimited String - UDF distinctstring

I encourage my readers to send their contribution as well so I can include their contribution as well.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation
Next Post
SQLAuthority News – Ahmedabad User Group Meeting January 17 2009

Related Posts

24 Comments. Leave new

  • Hi, Can anyone help me out to delete consecutive duplicate words in a string through SQL function

    Reply

Leave a Reply

Menu