Search SQL Server string for values from another t

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 + '%'

Answer 2

  select B.Name, A.Name from tableA A inner join join tableB B
  on rtrim(ltrim(B.Name)) like '%' + rtrim(ltrim(A.Name)) + '%'