Select Matching records in 2 tables - loose

[MikeL91]

I need to select all records in tableA where the name matches the name in tableB, but I want the match to be as loose as it can be so I can still match after MAJOR spelling errors.

Can I adjust the matching degree? and is there a way to veiw both fields in the querry window?


thanks in advance,
Mike

 

[Mike Lewis]

Mike,

First, to find exact matches, you would do this:

SELECT * FROM TableA ;
  WHERE Name IN ;
  (SELECT Name FROM Table B)

To introduce a degree of fuzziness in the match, you have several options:

- Use the LIKE operator. This essentially lets you search for substrings. I suspect it's not what you want.

- Use the DIFFERENCE() function, which measures the difference in the phonetic representation of two strings. It has several weaknesses, but basically does what you want.

- Use a more complex algorithm, such as my SIMILAR() function, which attempts to measure the similarity between two strings. The code was published in FoxPro Advisor, November 2002 (

http://msvfp.advisorguide.com/doc/10996).

(Sorry, I'm now allowed to re-publish it here.) Note that you cannot optimise a query using this routine, so it will be slow.

Hope this helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[alvechurchdata]

Fox has a Soundex() function which might help. It generates a phonetic code from a string so that you can select all the similar-sounding words.

For example: "Smith", "Smythe", "Schmit" and "Schmidt" all generate the code "S530" so:

lcTarget = Soundex("Smith")
Select * from myTable Where Soundex(myName) = lcTarget

will select all these varieties of "Smith"

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MikeL91]

Thanks for the help. I have another quick question, on this command:

SELECT * FROM TableA ;
WHERE Name IN ;
(SELECT Name FROM Table B)

This will show all records in TableA with a name that is = name in TableB.

Is there a way in this command to include the names from TableB that matched to tableA?

ex. TableA
JOHN DO 123 ANY STREET 92844

ex. Display AFTER querry:
JOHN DO 123 ANY STREET 92844

ex. Display I am looking for:
tableA.name tableA.address tableA.zip tableB.name
JOHN DO 123 ANY STREET 92844 JOHN DO

 

[Mike Lewis]

I think this should do it (although I'm not sure why you would want to, given that, by definition, the name is the same in both tables):

SELECT a.Name, a.Address, a.Zip, b.Name ;
  FROM TableA a JOIN Table b, ;
  ON aName = b.Name

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk