删除字符串sql表中的重复值

I have a table with values

我有一张有价值观的桌子

ID Names     
1  Adam,eve,adam,rick 
2  Don,jack,rick,don

Need to remove duplicate values for names column: out put should be like the following

需要删除名称列的重复值:out put应如下所示

1 Adam,Eve, Rick
2 Don Jack,Rick

3 个解决方案

#1

You can do this using a splitter function. Here is one taken from Aaron Bertrand's article.

您可以使用拆分器功能执行此操作。这是一篇来自Aaron Bertrand的文章。

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Then, your final query would be:

然后,您的最终查询将是:

SQL Fiddle

SELECT *
FROM @tbl t
CROSS APPLY(
    SELECT STUFF((
        SELECT DISTINCT ',' + Item
        FROM dbo.SplitStrings_XML(t.Names, ',')
        FOR XML PATH('')
    ), 1, 1, '')
)x(Item)

RESULT

| ID |                       Names |                  Item |
|----|-----------------------------|-----------------------|
|  1 |          Adam,eve,adam,rick |         Adam,eve,rick |
|  2 | Hello,World,Something,Hello | Hello,Something,World |

#2

Database data is tabular you need to move from this approach, save only one name in each row and the you can just grop by name and add a count, any name with more than one in the count are the duplicates then use a max or a min to get the id you want to delete a use that as a source for a delete statement.

数据库数据是您需要从这种方法移动的表格,每行只保存一个名称,您只需按名称编写并添加计数,计数中包含多个名称的任何名称都是重复项,然后使用最大值或者min获取要删除的id作为delete语句的源的用法。

#3

by using simple Stuff Function and Split we can achieve this

通过使用简单的Stuff Function和Split我们可以实现这一点

 declare @t table (Id int,value varchar(50))
    insert into @t (Id,value)
    values 
    (1,'Adam,eve,adam,rick'),
    (2,'Don,jack,rick,don')
    ;with cte as (
    SELECT ID,  
         Split.a.value('.', 'VARCHAR(100)') AS String  
     FROM  (SELECT ID,  
             CAST ('<M>' + REPLACE([value], ',', '</M><M>') + '</M>' AS XML) AS String  
         FROM  @t) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

         Select DISTINCT  C.ID,
         STUFF((select DISTINCT +','+ string 
            from cte where ID = C.ID 
         GROUP BY ID,string FOR XML PATH(''))
        , 1, 1, '')Val from cte C GROUP BY C.ID,C.string

#1