pattern search between tables

[Beantree]

Is there a way to do a pattern search between 2 tables.

For example, I have a table of name and address info (NAME_ADDRESS), but it is organized in fields using Line1, Line2, Line3, Line4, ... etc. so the user could enter anything in any line (I didn't design it). I have another table with some name and address info (SEARCH). I want to find pattern matches in the table NAME_ADDRESS that exist in SEARCH.

There are around 80,000 records in NAME_ADDRESS and 200 records in search.

 

[jee]

You could do something like this:

SELECT s.whatever, n.whatever
FROM   search s, name_address n
WHERE  n.line1 || n.line2 || n.line3 || n.line4 LIKE
       '%' || s.whatever || '%'
ORDER BY s.whatever;

Depending on your platform, etc., this could be pretty slow.