Search SQL Server string for values from another table

Question

I have a table with column name that has random characters before and after the name ie:

Table A:

  Name
  -----------------
  asd4345JONlkj345
  .;lidDavidlksd$

and I have another table in same DB that has the names ie:

Table B:

 Name
 ------
 David
 Jon

This goes on like this for 30k rows or I'd just hardcode something really quick. I want to search each string in Table A 'Name' column for each value from Table B, and if found return the name in a new column.

I have a feeling this will be a UDF, which is fine, I'm just unsure how to use patindex in this scenario, or if that is even the right approach.

Answer 1

You only need the LIKE operator for this:

select * 
from TableA
inner join TableB on TableA.Name like '%' + TableB.Name + '%'