Remove Duplicate from columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Remove Duplicate from columns

Author

Topic

tapori2010
Starting Member

2 Posts

Posted - 2011-05-10 : 14:39:11

Hi, how could I find and remove duplicates from a field? Not remove duplicate rows but dups in a colum?

Example:

Before
-------
Company | AreaCode
ABC | 972,214,972,972

After
-------
Company | AreaCode
ABC | 972,214

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-10 : 15:06:27

[code];WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<500),
Comp(Comp, Area) AS (SELECT Company, ',' + AreaCode + ',' FROM myTable),
Areas(Comp, Area) AS (SELECT Comp, SUBSTRING(Area, n, CHARINDEX(',', Area, n)-n)
FROM Comp CROSS JOIN n WHERE n<LEN(Area)+1 AND SUBSTRING(Area, n-1, 1)=','),
UniqueAreas(Comp, Area) AS (SELECT DISTINCT Comp, Area FROM Areas)
SELECT DISTINCT Comp, STUFF((SELECT ',' + Area FROM UniqueAreas WHERE Comp=Z.Comp FOR XML PATH('')),1,1,'')
FROM UniqueAreas Z
OPTION (MAXRECURSION 500)[/code]

tapori2010
Starting Member

2 Posts

Posted - 2011-05-10 : 15:26:43

Thanks. Found a function that did exactly what i was looking for.

http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-10 : 15:33:52

You should change the model - perhaps add a companyAreaCode table which has a row for each company and areacode. Then if you want to display them as a csv you can always aggregate them but they should be stored individually - for a lot of reasons.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources