Find similar records

[janusmccarthy]

Okay, this one stumped me.

We have an oracle 9 database, and I'll eventually be using PHP 5.2.x to eventually do the update, but for now, I'm using Oracle SQL*Plus.

At this stage, we're looking to remove nearly duplicate entries.

As a simple example, suppose we had a table of male first names and would like to identify names such as 'Doug' vs 'Douglass' and candidates, as well as any other name that starts similarly or at least has a number of letters in common, such as 'Fred' and 'Frederick' and 'Greg' and 'Gregory'.

Any ideas?

[janusmccarthy]

Figured it out:

 SELECT NAME
   FROM TABLE_NAME a
   WHERE EXISTS (
     SELECT NULL
     FROM TABLE_NAME b
     WHERE a.NAME LIKE CONCAT( SUBSTR( b.NAME, 1, 4), '%')
     AND a.NAME != b.NAME
   )