Remove duplicate values from the same cell

Question

Hi All,

If in one cell I have a value like - text1, text1, text2, text2, text3

How do I get it to text1, text2, text3 please

thanks in advance

Answer 1

I found this function and it gave me what I needed.

Create FUNCTION dbo.RemoveDuplicate
(
@StringList 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 @StringList = LTRIM(RTRIM(@StringList)) + @Delim
SET @POS = CHARINDEX(@delim, @StringList, 1)
WHILE @POS > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@StringList, @POS - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @StringList = SUBSTRING(@StringList, @POS +1, LEN(@StringList))
SET @POS = CHARINDEX(@delim, @StringList, 1)
END
SELECT @rlist = COALESCE(@rlist+@Delim,'') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO

Answer 2

In the future you can also try something like this too:

select *,
    (
        select string_agg(value, ', ') within group (order by value) d
        from (select distinct trim(value) as value from string_split( MyColumn, ',')) d
    )
from MyTable