2

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?

Bruce
  • 565
  • 2
  • 8
  • 23

6 Answers6

3

You could create this function:

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 then use it like this:

 Declare @address varchar(300)='AUNDH AUNDH CAMP'
 SELECT dbo.RemoveDuplicate(@address,' ') -- The delimiter is an empty space
MarredCheese
  • 17,541
  • 8
  • 92
  • 91
K.K
  • 366
  • 3
  • 12
0

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.

A_Sk
  • 4,532
  • 3
  • 27
  • 51
0

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.

Paweł Dyl
  • 8,888
  • 1
  • 11
  • 27
0

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
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

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.

Serg
  • 22,285
  • 5
  • 21
  • 48
0
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
Михаил Сергеев
  • 1