title: Matching Oracle duplicate column values using Soundex, Jaro Winkler and Edit Distance (UTL_MATCH)
body: <p>I am trying to find a reliable method for matching duplicate person records within the database. The data has some serious data quality issues which I am also trying to fix but until I have the go-ahead to do so I am stuck with the data I have got.</p>
  
  <p>The table columns available to me are:</p>
  
  <pre><code>SURNAME       VARCHAR2(43)
  FORENAME      VARCHAR2(38)
  BIRTH_DATE    DATE
  ADDRESS_LINE1 VARCHAR2(60)
  ADDRESS_LINE2 VARCHAR2(60)
  ADDRESS_LINE3 VARCHAR2(60)
  ADDRESS_LINE4 VARCHAR2(60)
  ADDRESS_LINE5 VARCHAR2(60)
  POSTCODE      VARCHAR2(15)
  </code></pre>
  
  <p>The <code>SOUNDEX</code> function is relatively limited for this use but the <code>UTL_MATCH</code> package seems to offer a better level of matching using the Jaro Winker algorithm.</p>
  
  <p>Rather than re-inventing the wheel, has anyone implemented a reliable method for matching this type of data?</p>
  
  <p>Data Quality issues to contend with:</p>
  
  <ol>
  <li>The postcode, though mandatory, isn't always fully entered.</li>
  <li>The address data is relatively poor quality with addresses entered in no fixed format (i.e. some may have line1 as "Flat 1" whereas some may have line1 as "Flat1, 22 Acacia Ave").</li>
  <li>The forename column can contain an initial, a full forename or sometimes more than one forename.</li>
  </ol>
  
  <p>For example I was considering:</p>
  
  <p>Concatenating all address fields and applying the Jaro Winkler algorithm to the full address combined with a similar test of the full name concatenated together.</p>
  
  <p>The birth date can be compared directly for a match but due to the large volume of data just matching upon this isn't enough. </p>
  
  <p>Oracle 10g R2 Enterprise Edition.</p>
  
  <p>Any helpful suggestions welcome. </p>