Distinct data question

[smeyer56]

We have a website database that customers enter their own data in. The data is stored in an SQL server 2000 db. The issue is that customers might enter information multiple times and we all know how bad spelling can be. I have users how are in a table many times that are obviously distinct records but i cannot figure how to get only one copy since the name or address might be off by a letter or two.
IE
1.Name john jones Company myCompany
2.Name john jonnes Company myCompany

Is there a way to do this in a SQL query?

 

[PruSQLer]

That would have to be SOME query to determine duplicates of that nature. Obviously, GROUP BY won't work. Best thing to do would be to clean up the data and revise the front end so each user is assigned a user ID. Then they would sign in using that ID so that address and whatever else is used as a unique key would be retrieved by the controlling program. That would eliminate errors.

Sorry, I know this wasn't what you were looking for. Maybe someone else here has an extremely smart query...

 

[JarlH]

Try to combine a case and accent insensitive collation with SOUNDEX, and removing all whitespace characters.

 

[smeyer56]

Could you be more specific i am not sure I understand what you said?

 

[PHV]

Take a look here:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_setu-sus_3o6w.asp

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244