title: How to join two table with partial match
body: <p>I have two table with the following data:</p>
  
  <pre><code>TableA.name
  R4.23-Core-2
  R4.23-Core-2
  LA#213 CGHPBXsw01 127.213 0024-737e-e341
  LA#252 CGHRack1sw01 127.252 0022-57ab-d781
  SOC-01A-SW01
  to - R4-DISTR-9512
  to-R2-DISTR-5900-1
  to-R3.25-EDGE
  
  TableB.caption
  R4.23-Core-2.ehd.ca
  R4.23-Core-2.nhd.ca
  CGHPBXsw01
  CGHRack1sw01
  SOC-01A-SW01
  R4-DISTR-9512
  R2-DISTR-5900-1.phsnc.
  R3.25-EDGE.phsne.edjc.ca
  </code></pre>
  
  <p>I've tried using the following join statement but it doesn't seem to work for any row with a . in it.</p>
  
  <pre><code>dbo.TableA.Name 
  INNER JOIN dbo.TableB.Caption 
    ON dbo.TableA.Name LIKE '%' + dbo.TableB.Caption + '%'
  </code></pre>
  
  <p>I also try using replace function, which work but there are too much variant to include with replace. </p>
  
  <p>I could try using the RIGHT or LEFT function to normalize the data but for row that doesn't have '.' it would throw an error. And I don't know how to skip row that doesn't have '.'</p>
  
  <p>What is the most efficient way to join these two table?</p>