title: What are ways to match street addresses in SQL Server?
body: <p>We have a column for street addresses:</p>
  
  <blockquote>
    <p>123 Maple Rd.<br>
    321 1st Ave.<br>
    etc...</p>
  </blockquote>
  
  <p>Is there any way to match these addresses to a given input?  The input would be a street address, but it might not be in the same format.  For example:</p>
  
  <blockquote>
    <p>123 Maple Road<br>
    321 1st Avenue</p>
  </blockquote>
  
  <p>Our first thought is to strip the input of all street terms (rd, st, ave, blvd, etc).</p>
  
  <p>Obviously that won't match reliably all the time.  Are there any other ways to try to match street addresses in SQL server?</p>
  
  <p>We can use user defined functions, stored procs and regular old t-sql.  We cannot use clr.</p>