Search SQL Server string for values from another table
<p>I have a table with column <code>name</code> that has random characters before and after the name ie:</p>

<p><strong>Table A</strong>:</p>

<pre><code>  Name
  -----------------
  asd4345JONlkj345
  .;lidDavidlksd$
</code></pre>

<p>and I have another table in same DB that has the names ie:</p>

<p><strong>Table B</strong>:</p>

<pre><code> Name
 ------
 David
 Jon
</code></pre>

<p>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.</p>

<p>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. </p>