表格单元格包含由逗号分隔的多个值，例如A，B，C，D，A，E，C，然后如何删除重复值 [英] A table cell contains multiple values separated by commas e.g A,B,C,D,A,E,C then how to remove the duplicate values

问题描述

create table table_1（id int identity，name varchar（50））



插入table_1值（'abc，jkm，pqr，abc，def，ghi '，jkm.ghi）

插入table_1值（'mno，pqr，abc，mno，pqr'）

插入table_1值（'abc，jkm，stu ，jkm，rcm，tin'）



我想删除重复的名称。

in id = 1 name is ='abc， jkm，pqr，abc，def，ghi，jkm，ghi'

现在删除重复名称后应为='abc，jkm，pqr，def，ghi'

create table table_1 (id int identity,name varchar(50))

insert into table_1 values ('abc,jkm,pqr,abc,def,ghi',jkm.ghi)
insert into table_1 values ('mno,pqr,abc,mno,pqr')
insert into table_1 values ('abc,jkm,stu,jkm,rcm,tin')

I want to remove the duplicate name.
in id=1 name is ='abc,jkm,pqr,abc,def,gjkm,ghi'
now after remove the duplicate name should be='abc,jkm,pqr,def,ghi'

推荐答案

看看例子：

Have a look at example:

DECLARE @tmp VARCHAR(30)

DECLARE @t TABLE (RowNo INT, MyValue VARCHAR(10), Remainder VARCHAR(30))


SET @tmp = 'A,B,C,D,A,E,C'

;WITH MyCTE AS
(
	SELECT 1 AS RowNo, LEFT(@tmp, CHARINDEX(',',@tmp)-1) AS MyValue,  RIGHT(@tmp, LEN(@tmp) - CHARINDEX(',',@tmp)) AS Remainder
	WHERE CHARINDEX(',',@tmp)>0
	UNION ALL
	SELECT RowNo +1 AS RowNo, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS MyValue,  RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder
	FROM MyCTE
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT RowNo +1 AS RowNo, Remainder AS MyValue,  NULL AS Remainder
	FROM MyCTE
	WHERE CHARINDEX(',',Remainder)=0
)
INSERT INTO @t (RowNo, MyValue, Remainder)
SELECT RowNo, MyValue, Remainder
FROM MyCte 

--display MyCTE data
SELECT *
FROM @t

--get duplicates
SELECT MyValue, COUNT(MyValue) AS CountOfMyValue
FROM @t
GROUP BY MyValue
HAVING COUNT(MyValue)>=1

在上面的例子中公用表表达式 [ ^ ]已用于将字段值拆分为一组记录。



您没有提供足够的信息来帮助您。请改进你的问题，我将保证更新我的答案，告诉你如何删除重复项（更新字段）。









我很高兴更新这个问题。最终解决方案是......

In above example Common Table Expression[^] has been used to split field values into set of records.

You did not provide enough information to help you more. Please, improve your question and i'll promise to update my answer to show you how to remove duplicates (update field).




I'm glad on update the question. The final solution is...

DECLARE @srctbl TABLE (id INT IDENTITY(1,1), names VARCHAR(50))

INSERT INTO @srctbl VALUES ('abc,jkm,pqr,abc,def,ghi,jkm,ghi'),
('mno,pqr,abc,mno,pqr'), ('abc,jkm,stu,jkm,rcm,tin')

DECLARE @dsttbl TABLE (id INT, RowNo INT, MyValue VARCHAR(10), Remainder VARCHAR(30))
 

;WITH MyCTE AS
(
	SELECT id, 1 AS RowNo, LEFT(names, CHARINDEX(',',names)-1) AS MyValue,  RIGHT(names, LEN(names) - CHARINDEX(',',names)) AS Remainder
	FROM @srctbl 
	WHERE CHARINDEX(',',names)>0
	UNION ALL
	SELECT id, RowNo +1 AS RowNo, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS MyValue,  RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder
	FROM MyCTE
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT id, RowNo+1 AS RowNo, Remainder AS MyValue,  NULL AS Remainder
	FROM MyCTE
	WHERE CHARINDEX(',',Remainder)=0
)
INSERT INTO @dsttbl (id, RowNo, MyValue, Remainder)
SELECT id, RowNo, MyValue, Remainder
FROM MyCte 
 

UPDATE t3 SET  t3.names = t4.names
FROM @srctbl AS t3 INNER JOIN (
	SELECT  t1.id, STUFF( (SELECT ',' + MyValue AS 'text()' 
					  FROM (
	  					SELECT id, MyValue 
						FROM @dsttbl 
						GROUP BY id, MyValue
						HAVING COUNT(MyValue)>=1
					  ) AS t2
					  WHERE t2.id = t1.id
					  FOR XML PATH('')), 1, 1, '') AS [names]
	FROM @dsttbl  AS t1
	GROUP BY t1.id
) AS t4 ON t3.id = t4.id 

SELECT *
FROM @srctbl 

[/ EDIT]

简单：

1）以字符串形式获取单元格内容。

2）将此字符串拆分为字符串数组（提示： String.Split方法 [ ^ ]）。

3）构造一个HashSet<string> [ ^ ]来自第2点获得的数组。

4）构造使用存储在HashSet中的值的最终字符串。



祝你好运。

Simple:
1) Get your cell content as a string.
2) Split this string into an array of strings (tip: String.Split Method[^]).
3) Construct a HashSet<string>[^] from the array obtained at point 2.
4) Construct your final string using values stored in HashSet.

Good luck.

这篇关于表格单元格包含由逗号分隔的多个值，例如A，B，C，D，A，E，C，然后如何删除重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！