Combine Tables

Question

Combine Tables

zero98atm

SSC Enthusiast

Points: 115
More actions
February 11, 2003 at 3:31 pm

#158830

Okay, this is probably something fairly simple, but I'm fairly new to SQL. I have two tables. In each table is an address field. I want to combine these two tables on this address field (seems like a simple join so far), however the addresses aren't always the same in both tables, so I'd have to do something like using a LIKE statement. What I'm really getting at is doing some kind of join or something based on fuzzy matching, while discarding the records that aren't remotely matching. Any ideas?
Thanks,
Matt

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Wang Default port Points: 1463 More actions · Answer 1

Are you comparing the whole address + city + state + zip? I know at the place I work, sales people entered all sorts of crap into the app -> database. Like phone numbers, what I do is I use REPLACE(phone, 'x', '') which in this example replace all the 'x' in the "phone" string into '' zero length string. I use REPLACE to clean the phone numbers so they become standardized and then I match them for duplicates. My 2 cents, not sure if it can help u.

Nick

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

You might try something like this. This is a very simple example, you will more than likely need a more complex like clause.

create table x (id int, addr char(50))

create table y (id int, addr char(50))

insert into x values ('1','abcdefg')

insert into y values ('1','bcdefg')

insert into y values ('2','bbcdefg')

select * from x join y on

x.addr like '%' + rtim(y.addr)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

Probable should change that rtm to rtrim, like so: