Click here to Skip to main content
14,696,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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,ghi,jkm,ghi'
now after remove the duplicate name should be='abc,jkm,pqr,def,ghi'
Posted
Updated 24-Feb-14 20:06pm
v2

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).


[EDIT]

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]
   
v2
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.
   
Comments
Maciej Los 24-Feb-14 4:20am
   
Phil, the question is tagged as 'SQL'. At this moment, your answer is bit off-topic.
phil.o 24-Feb-14 4:22am
   
Hello Maciej, you are right. Thank you for reporting it :)
@OP: sorry for misreading the tag; you can ignore this solution if it is useless for you (I do not have an easy solution with SQL in mind - maybe someone else does?).
Maciej Los 24-Feb-14 4:30am
   
BTW: My 4 (reason: off-topic) and i would say: it's very good answer in general ;)
phil.o 24-Feb-14 4:33am
   
Thank you ; I still learn a lot from yours, btw ;)
Maciej Los 24-Feb-14 4:38am
   
Thank you on behalf of CodeProject community ;)
Cheers!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900