MySql - Partial String Match. Need help.

Databases
1

Hi,

I have two tables: A and B

Table A contains one column listing name initial + family name:

TableA.Column1
B . White
F. White
M. Black
J. Black
M. Green

Table B contains one column listing name + family name too:

TableB.Column1
B. White
F. White
R. Jackson

If I’d wish to extract from Table A the rows containing the name + family that are present inside table B I’d write the following code:

select TableA.Column1 from TableA, TableB where TableA.Column1 = TableB.Column1;

What if I had table B containing only family name in common with table A, as the following one:

TableB.Column1
T. White
G. Jackson

How can I match the B. White and F. White rows from table A with T. White from table B, so that I can extract from table A the rows that have the same family name from the family names from table B?

Thanks for any help.
Best Regards,
Antonio

2

assuming all names are in fact “initial” “period” “name”

where substr(tablea.column1,2)=substr(tableb.column1,2)

otherwise is get a bit more complex

3

Dave, thanks for helping,
Infact, the problem might be more complex and my example was not the best. So I found another example that should suit better.

Table A contains the address names so we may have for the same location address:

TableA.Column1
Station Road
Station Rd.

on table B we have:

TableB.Column1
Station Road

So I would like to extract both “Station Road” and “Station Rd.” from tableA when compared to TableB.

4

Normalize your addresses before you put them in the database.

5

Thanks Tim,
Maybe, should I , for example, transform all “Rd.” occurances into “Road”, as first step?
I also read something about the MATCH() function. May that be a possible solution to my problem?

6

Anyway the problem might arise not only with Road Vs. Rd. but also with “Station” vs a miswritten “Staion”, “Statio” and so on. So, in the end, I wish to extract the rows not only when they are equal but also when they are similar, until a certain grade of similarity is present.

7

Antonio, try to use Phonetik search (SOUNDEX). It might be a good solution for you.
Find more info here:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_soundex