Sql query with Wildcard search

Hello BADDULA ,

Could you please share your logic used for output shared ? Are you using SQL Server or different SQL? Your output does not give clear logic:

1. row 2 and 5 from table1 is same, how did you find only one entry in output with TID=2 only ?
2. Second row of output pick TID=11 that is exact match between Table1 and Table2. Why did you ignore row 3 from table1 with TID=3. It has "Mouse" in the convetcolumn .

  Please share exact logic to check further.

If you simply want to find all matching values (Case -Insensitive), use below SQL

SELECT T1.TID,T2.Reference
FROM Table_1 AS T1
     INNER JOIN
     Table_2 AS T2
         ON T1.ConvertColumn LIKE N'%' + T2.Reference + N'%'

If you want to find all matching values (Case -sensitive), use below SQL

SELECT T1.TID, T2.Reference
FROM Table_1 AS T1
INNER JOIN Table_2 AS T2
    ON T1.ConvertColumn COLLATE Latin1_General_CS_AS 
       LIKE N'%' + T2.Reference COLLATE Latin1_General_CS_AS + N'%'

If you don't want values from Table1 ,only matching values from Table2. use this query (better performance)

SELECT T2.*
FROM Table2 T2
WHERE EXISTS (
    SELECT 1
    FROM Table1 T1
    WHERE T2.target_column LIKE '%' + T1.pattern_value + '%'
)

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Kind Regards,

Neeraj