title: How to compare two columns for to see if the same text is present on both
body: <p>I'm working in SSMS v18.2.</p>
  <p>I am comparing some messy addresses to see if they are essentially the same. I receive address as 4 separate columns, Address Line1, Address Line2 , Address Line3 and Address Line4. I am trying to compare that address to a manually typed address. Often, the addresses are similar, but the manually typed one may have spelling errors or be a shortened version of the full address. I have concatenated AddressLine 1 to 4 into one string and removed any spaces:</p>
  <p><code>REPLACE(CONCAT(AddressLine1,AddressLine2,AddressLine3,AddressLine4),' ','') AS CorrectedAddress</code></p>
  <p>I am then trying to compare this address to the manually entered address which I have tidied up with:</p>
  <p><code>REPLACE(ManualAddress,' ','') AS TidyManualAddress</code></p>
  <p>I am trying to work out the best way to compare the two. Basically, what I'd like to say is if wherever the text in the TidyManual Address appears in the CorrectedAddress, then Y else N.</p>
  <p>I've tried the following:</p>
  <p><code>CASE WHEN SUBSTRING(TidyManualAddress,1,8) = SUBSTRING(CorrectedAddress,1,8) THEN 'Y' ELSE 'N' END AS AddressMatch</code></p>
  <p>However, the choice of 8 characters in the substring function is just arbitrarily chosen.</p>
  <p>Is there a way to compare two columns to see if the string from one column is found in another?</p>
  <p>As an example if my CorrectedAddress is <em><strong>MovingPlace,MadeUpTown,SomeCountry</strong></em> and my ManualAddress is <em><strong>MovingPlace</strong></em>, I'd like to return a yes as MovingPlace is found in the string on the first column. My problem obviously is the varying text lengths of each string.</p>