Join and Functions for Searching?

[bairdgbaird]

How can I search for LASTNAME and FIRSTNAME fields from TABLE_1 contained in NAME in TABLE_2?

TABLE_1 contains 80,000 records and has FIRSTNAME and LASTNAME fields.

TABLE_2 has 1 million records and just one field for NAME. Complicating matters, this NAME field is not uniformly formatted. NAME sometimes includes includes middle initials. It also sometimes contains two names, again, not always in a uniform fashion. So it could include the following entries:
1. Doe, John
2. Doe, John F.
3. John Doe
4. John F. Doe
5. Doe, John & Jill
6. Doe, John F. & Jill
7. Doe, Jill & John

I want to return matches where people from TABLE_1 appear in TABLE 2. I know I can do a series of left, right, mid, and instr functions to split NAME in TABLE_2, but with 1 million records that's a lot of work and am looking for a smarter approach.

Please help.

 

[tweedle]

My $.02; You need to go through the pains to normalize Table 2.
On 80000 records, every 800 mis-matches is a 1% error ratio.
At that, I would have little confidence in the final results.
You'll never get a stable data set the way the data is now.