SQL question...

From: Sayoni <sayoni_at_mosdata.com.au>
Date: Mon, 24 Jan 2000 09:19:16 +1100
Message-ID: <000301bf65ef$e2dbada0$2bc809c0@sayoni>

Hi guys,
I was wondering if anyone can help me write this query..I am a newbie and don't really know the intricacies yet...

I have two tables....
TableA (id int, firstname,streetno, streetname,houseno,city) TableB (id int, firstname,streetno, streetname,houseno,city)

TableA has all "correct" data and TableB may have some spelling mistakes in the firstname. eg. it may have 'Sudan' instead of 'Susan' . Now what I would like to do is, look through TableB and try to match the firstname with the firstname of TableA where everything else matches except the firstname.

So basically

TableA.streetname = TableB.streetname and
TableA.streetno = TableB.streetno and
TableA.houseno = TableB.houseno and
TableA.city= TableB.city and

substr(TableA.firstname,1,1) = substr(TableB.firstname,1,1)

..then go about replacing one letter at a time and match it with the rest. If it matches then basically I have found a match... eg...
Find the length of the firstname and loop through the firstname length no of times.

In the first round I will replace 'S?dan' and match it with 'S?san' it does not match so I loop through again and say 'Su?an' and match it with 'Su?an'
it matches so i exit the loop and consider it matched.

does it all make sense? thanks a lot in advance.

Sayoni Mookerjee
Snr. Analyst Programmer
MOS Data, North Sydney
(Work) : +61 (02) 9510 1370
(Mobile) :+61 (041) 322 1277

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Sun Jan 23 2000 - 16:19:16 CST