Aparna Aparna - 2 months ago 8
SQL Question

How to find duplicate text in a column and remove duplicate in sql server

In the following example

address: AUNDH AUNDH CAMP


I want to remove the duplicate and the result must be

address: AUNDH CAMP


How to perform this in sql server?

Answer

By creating a below function,u can achive,

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

And you can select it by

 Declare @address varchar(300)='AUNDH AUNDH CAMP'
 SELECT dbo.RemoveDuplicate(@address,',')