Duplicate record query - 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 2008 Forums

Transact-SQL (2008)

Duplicate record query

Author

Topic

smngd68
Starting Member

4 Posts

Posted - 2011-09-23 : 13:18:13

I’ve created a query for finding duplicate company records (based on the company text string). In the example below I’m comparing the first 12 characters of the name field for duplicates.

What’s odd is it works however my result set only includes companies that begin with C. Any thoughts?

select x.ClientID,RTRIM(x.Name)
from CL x
where exists (select RTRIM(x.Name)
from CL y
where RTRIM(SUBSTRING(x.Name,0,12)) = RTRIM(SUBSTRING(y.Name,0,12))
Group by y.Name
Having COUNT(*) > 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 13:42:13

May be those are the only records which satisfy your condition. Other than cant spot anything fishy in the posted code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smngd68
Starting Member

4 Posts

Posted - 2011-09-23 : 13:47:59

That was my first thought as well, however when I change the string length from 12 to 3, the result set continues to be limited to Names that begin with C. Odd.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 13:51:53

i think there's a small typo in code


select x.ClientID,RTRIM(x.Name)
from CL x
where exists (select RTRIM(y.Name)
from CL y
where RTRIM(SUBSTRING(x.Name,0,12)) = RTRIM(SUBSTRING(y.Name,0,12))
Group by y.Name
Having COUNT(*) > 1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smngd68
Starting Member

4 Posts

Posted - 2011-09-23 : 14:00:38

Good catch. Always helps to have an additional set of eyes. No luck however.

If I use a different approach (sans the substring) I continue to only see results beginning with C. I know there are others. CL is a table containing Company records.

select RTRIM(x.Name)
from CL x
where 1 < (select COUNT(*)
from CL y
where RTRIM(y.Name) = RTRIM(x.Name))

Results...

Cresa...
Community...

Nothing else. There should be several that begin with S (visually confirmed). Very odd.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 14:14:09

are you using a case sensitive collation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-09-23 : 14:17:01

maybe add LTRIM also?

If you don't have the passion to help people, you have no passion

smngd68
Starting Member

4 Posts

Posted - 2011-09-23 : 14:28:01

Found it. I was missing rtrim/substring from the group by. The following works matching the first five characters in Name.

select x.[Name]from [CL] x
where exists (select SUBSTRING(y.[Name],1,5)
from [CL] y
where SUBSTRING(y.[Name],1,5) = SUBSTRING(x.[Name],1,5)
Group by SUBSTRING(y.[Name],1,5)
Having COUNT(*) > 1)
Order by 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources