MSSQL – remove duplicates from string

Create and call function

CREATE FUNCTION dbo.PK_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

and then call:

SELECT dbo.PK_DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') as DistinctList

OR

SELECT dbo.PK_DistinctList(tbl_persons.phone_numbers,',') as DistinctPhones

Source: https://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/

This entry was posted on Thursday, January 10th, 2019 at 3:28 pmand is filed under MSSQL Database Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.