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

DECLARE @source TABLE
(
  [str] VARCHAR(MAX)
)

INSERT INTO @source
SELECT 'address: AUNDH AUNDH CAMP'
UNION ALL
SELECT 'address: AUNDH CAMP AUNDH'
UNION ALL
SELECT 'address: BBB AUNDH address:'
UNION ALL
SELECT 'address: BBB AUNDH CAMP'

DECLARE @tbl AS TABLE
(
  num INT,
  [str] VARCHAR(MAX)
)

DECLARE @result AS TABLE
(
  num   INT,
  n     SMALLINT,
  [str] VARCHAR(MAX)
)

INSERT INTO @tbl
SELECT ROW_NUMBER() OVER (ORDER BY [str]), [str]
FROM   @source

DECLARE
  @i INT = 0,
  @max_i INT = (SELECT MAX([num]) FROM @tbl),
  @str VARCHAR(MAX),
  @n SMALLINT

WHILE(@i < @max_i)
BEGIN

  SET @str = (SELECT [str] FROM @tbl WHERE [num] = @i + 1)

  SET @n = 1

  WHILE(CHARINDEX(' ', @str) <> 0)
  BEGIN

    INSERT INTO @result
    SELECT num, @n, SUBSTRING(@str, 1, CHARINDEX(' ', @str) - 1) FROM @tbl WHERE [num] = @i + 1

    SET @str = SUBSTRING(@str, CHARINDEX(' ', @str) + 1, LEN(@str))

    SET @n += 1

  END

  INSERT INTO @result
  SELECT num, @n, @str FROM @tbl WHERE [num] = @i + 1

  SET @i += 1
END

-- result
SELECT SUBSTRING([str], 2, LEN([str])) AS [str]
FROM   (
         SELECT DISTINCT r_big.num,
                (
                  SELECT ' ' + [str] AS [text()]
                  FROM   @result r_small
                  WHERE  r_small.num = r_big.num
                  GROUP BY r_small.num, r_small.[str] 
                  ORDER BY num, min(n)
                  FOR XML PATH('')
                ) AS [str]
         FROM   @result r_big
       )x

If you are using SQL Server > 2016 then you can use STRING_SPLIT

Split the column value by space and select distinct column value and then concatenate and create your result column string.

Beware of this kind of processing in productiona databases. There is a lot to think of (is double always wrong, how to treat punctuation marks, are words separated by space only). However, you can use recursion, like in following snippet:

DECLARE @word varchar(MAX) = 'AUNDH AUNDH CAMP';

WITH Splitter AS
(
    SELECT 1 N, LEFT(@word,CHARINDEX(' ',@word,1)-1) Word, SUBSTRING(@word, CHARINDEX(' ', @word, 0)+1, LEN(@word)) Rest
    UNION ALL 
    SELECT N+1 N,
           CASE WHEN CHARINDEX(' ', Rest, 0)>0 THEN LEFT(Rest, CHARINDEX(' ', Rest, 0)-1) ELSE Rest END,
           CASE WHEN CHARINDEX(' ', Rest, 0)>0 THEN SUBSTRING(Rest, CHARINDEX(' ', Rest, 0)+1, LEN(Rest)) ELSE NULL END
    FROM Splitter
    WHERE LEN(Rest)>0
), Numbered AS
(
    SELECT N, Word, ROW_NUMBER() OVER (PARTITION BY Word ORDER BY N) RowNum
    FROM Splitter
)
SELECT STUFF((SELECT ' '+Word
              FROM Numbered
              WHERE RowNum=1
              ORDER BY N
              FOR XML PATH('')), 1, 1, '') NoDuplicates

You can embed this in a function if you wish.

Using Numbers table:

 create table #test
(
id varchar(max)
)

insert into #test
select 'a a b'
union all
select 'c c d'

;with cte
as
(select *,dense_rank() over ( order by id) as rownum
from
#test t 
cross apply
(select * from [dbo].[SplitStrings_Numbers](t.id,' '))b
)
,finalresult
as
(select 
  (
select ''+ item from cte c1 where c1.rownum=c2.rownum
group by item
for xml path('')
)as finalvalue
 from cte c2
 )
 select finalvalue from finalresult
 group by finalvalue

This is essentially the same idea as in @TheGameiswar's answer just a bit shorter, unnecessary steps excluded.

create table #test
(
id varchar(max)
)

insert into #test
select 'a a b'
union all
select 'c c d';

select *, 
  stuff((
    select ' '+ item 
    from  [dbo].[DelimitedSplit8K](t.id,' ') 
    group by item
    for xml path('')
  ),1,1,'') as finalvalue 
from #test t

DelimitedSplit8K is a fast string splitter from http://www.sqlservercentral.com/articles/Tally+Table/72993/ . You can use any other one at hand.