Q:

how to do instr in tsql join

I have a huge table with 1000 million  records
I want to match the records from tiny table with records in hundreds
In huge table there filed full_name ( name of person ,  firstname middlename lastname)
In tiny table have 3 fileds  (TINY_TABLE.NAME_PART    TINY_TABLE.NAME_PART      TINY_TABLE.NAME_PART)
But firstname / middlename/ lastname are mixed up in tiny_table.

Now I want to do inner join both tables and want to do fuzzy matching.
I am not using fulltext search / or contain command
Can we do this using join (like instr fuction in oracle)
if (
TINY_TABLE.NAME_PART_1  exists in  Huge_Table  
and
TINY_TABLE.NAME_PART_2   exists in  Huge_Table 
and
TINY_TABLE.NAME_PART_3   exists in  Huge_Table
)
then
    Mark Matched
else
    Mark UnMatched

A:

Try something like this. But this is not a efficient code.
SELECT DISTINCT a.fullname,
       'Matched' [status]
FROM  (select distinct fullname from huge_table) a
       JOIN tiny_table b
         ON a.fullname LIKE '%' + namepart1 + '%'
             AND a.fullname LIKE '%' + case when len(namepart2)=0 then ' ' else namepart2 end + '%'
             AND a.fullname LIKE '%' + namepart3 + '%'
UNION
SELECT *,
       'Not matched' [status]
FROM   huge_table 

