same address, but name was spelled different

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)

same address, but name was spelled different

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-08 : 11:49:28

hello,
Is there something we can do to pick out similar names and matching address’s to combine them? For example: in my table 1, there are one lawyer, but table reported 2 name with same address, but spell differnt...see example below:
LIST 1:
AARON S FRIEDMAN
ADDRESS: STE355 1617 JFK ST PHILADELPHIA, PA

LIST 2:
AARON S FRIEDNAMN
ADDRESS: SAME AS LIST 1

The correct name is list 2.

Thanks,

Please show me how to combine them so we don't duplicate sending letter twice.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-08 : 11:56:57

Blindman (a forum user here) has written a very nice "fuzzy match" function that you may find useful:

http://sqlblindman.googlepages.com/fuzzysearchalgorithm

- Jeff
http://weblogs.sqlteam.com/JeffS

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-08 : 12:14:26

If the addresses match exactly, you could do something like

select l1.name, l2.name
from list l1
inner join list l2
on l1.address = l2.address
where l1.name < l2.name

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-08 : 12:28:17

quote:
Originally posted by bjoerns

If the addresses match exactly, you could do something like
select l1.name, l2.name
from list l1
inner join list l2
on l1.address = l2.address
where l1.name < l2.name

I meat both listed names appeared in the same table 1, not in separate list. I put list 1, and 2 upthere to distingish 2 differrent spelling. So it won't work with your query. Thanks,

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-08 : 12:36:07

It works, because the same list has two different aliases (l1 and l2). I think that is called a self-join.

Bjoern

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-08 : 13:14:18

quote:
Originally posted by bjoerns

It works, because the same list has two different aliases (l1 and l2). I think that is called a self-join.

Bjoern

I will try and inform you the result...

Thanks very much!

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-08 : 14:29:21

quote:
Originally posted by ntn104

quote:
Originally posted by bjoerns

It works, because the same list has two different aliases (l1 and l2). I think that is called a self-join.

Bjoern

The query work but how can we only show one unique name?
Here is my sample result:
AARON S FRIEDMAN STE 355 PHILA PA 19103
AARON S FRIEDMAN STE 355 PHILA PA 19103
AARON S FRIEDMAN STE 355 PHILA PA 19103
AARON S FRIEDMANN STE 355 PHILA PA 19103

and the last one is the correct name. Thanks,

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-08 : 14:40:54

There is absolutely no way SQL can tell you which is the "correct" name.
Even in your example, I would have bet that "AARON S FRIEDMAN" was the correct name because it appears three times, while "AARON S FRIEDMANN" appears only once.

Boycotted Beijing Olympics 2008

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-09 : 12:02:53

quote:
Originally posted by blindman

There is absolutely no way SQL can tell you which is the "correct" name.
Even in your example, I would have bet that "AARON S FRIEDMAN" was the correct name because it appears three times, while "AARON S FRIEDMANN" appears only once.

Boycotted Beijing Olympics 2008

I guess I have to manually to delete names that I makes sense.

Thanks all!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 12:41:04

You want to manually delete records that makes sense?
Huh?

E 12°55'05.63"
N 56°04'39.26"

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-09-09 : 13:12:45

Yes, at some point it would seem a human has to look at the record to decide which is the correct one.

The fuzzy match still helps you though. Instead of wiping out records programmatically (and arbitrarily), you could output a list of similar records and give that entire list to a human, assuming this is a regular process that needs to run.

Just a thought.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources