14,696,430 members
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

Solution 2

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

Solution 1

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

Top Experts
Last 24hrsThis month
 OriginalGriff 310 Rick York 170 RickZeeland 120 Richard Deeming 105 BillWoodruff 80
 OriginalGriff 290 Rick York 120 Richard Deeming 105 BillWoodruff 80 RickZeeland 80

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