MySql - Partial String Match. Need help.


I have two tables: A and B

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

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

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

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:

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,

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

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:

Station Road
Station Rd.

on table B we have:

Station Road

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

Normalize your addresses before you put them in the database.

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?

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.

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