Oracle Fuzzy Text Matching

Oracle Text

Wanneer je door je je database wilt kunnen "Googelen" heeft Oracle een oplossing "Oracle Text" voor je (Oracle 10g). Configuratie en gebruik is echter vrij ingewikkeld maar dan heb je wel ook erg veel mogelijkheden, waarschijnlijk meer dan je zal gebruiken. Daarom worden op deze pagina een aantal andere mogelijkheden gegeven.

Meer info:

• "Oracle Text":http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

Like

Met _LIKE_ kan je wildcards gebruiken om textvelden te vergelijken. Hierin is '?' een enkel willekeurig karakter en '%' nul of meer willekeurige karakters:

select kolom 
  from tabel 
  where kolom like 'een?waarde%'
  ;

kolom
------
een waarde
een_waarde 123
een_waarde 12%
een%waarde ABC

Wanneer je bijvoorbeeld wilt zoeken op een '%' of een '?' kun je een escape karakter definiëren:

select kolom
  from tabel 
  where kolom like '%\%%' escape '\'
  ;

kolom
------
een_waarde 12%
een%waarde ABC

Soundex

Wat doe je als je twee test-waarden wilt vergelijken die niet _exact_ hetzelfde hoeven te zijn? Oracle heeft echter ook nog een functie *SOUNDEX* die een string omzet naar een klank-codering. Deze matching is erg grof maar bijzonder handig zijn als je niet _elke_ combinatie wilt zien:

select *
  from tabel 
  where soundex(kolom1) =  soundex(kolom2)
  ;

Package UTL_MATCH

Oracle 10g heeft een package UTL_MATCH met daarin een aantal functies waarmee een matching percentage berekend kan worden. In het volgende voorbeeld worden een aantal functies gebruikt om de teksten voor te bereiden, met een union query wordt een kleine testset gemaakt zonder dat er een tabel nodig is.

De volgende functies worden gebruikt:

• TRIM
  • spaties en tabs rondom de tekst verwijderen
• UPPER
  • alles hoofdletters maken
• CONVERT (kolom1,'us7ascii')
  • accenten vervangen, zoals in 'ëïñdhóvèn' -> 'eindhoven'
• UTL_MATCH.JARO_WINKLER_SIMILARITY(kolom1,kolom2)
  • jaro-winkler matching %, ("Wikipedia":http://en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance)
• UTL_MATCH.EDIT_DISTANCE_SIMILARITY(kolom1,kolom2)
  • levenshtein-distance matching %, ("Wikipedia":http://en.wikipedia.org/wiki/Levenshtein_distance)

select kolom1, kolom2
  , convert(Upper(Trim(kolom1)),'us7ascii')
  , convert(Upper(Trim(kolom2)),'us7ascii')
  , UTL_MATCH.JARO_WINKLER_SIMILARITY(convert(Upper(Trim(kolom1)),'us7ascii') , convert(Upper(Trim(kolom2)),'us7ascii')) as Winkler
  , UTL_MATCH.EDIT_DISTANCE_SIMILARITY(convert(Upper(Trim(kolom1)),'us7ascii'),convert(Upper(Trim(kolom2)),'us7ascii')) as Distance
  from ( 
    select 'Eindhoven' as kolom1, 'Veldhoven' as kolom2 from dual 
    union
    select 'Eindhoven', 'ëïñdhóvèn' from dual 
    union
    select 'Eindhoven ', 'EiNdHovEn' from dual 
    union
    select 'Eindhoven', 'Helmond' from dual 
  )
  order by Winkler desc
  ;

Met de volgende query kunnen we een 'beste' Winkler-match selecteren, door wel minimaal 95% te eisen voorkomen we ongewenste combinaties:

select * from (
  select kolom1, kolom2
    , UTL_MATCH.JARO_WINKLER_SIMILARITY(
          convert(Upper(Trim(kolom1)),'us7ascii')
        , convert(Upper(Trim(kolom2)),'us7ascii')) as Winkler
    from (
      select 'Eindhoven' as kolom1, 'Veldhoven' as kolom2 from dual
      union
      select 'Eindhoven', 'ëïñdhóvèn' from dual
      union
      select 'Eindhoven ', 'EiNdHovEn' from dual
      union
      select 'Eindhoven', 'Helmond' from dual
    )
    order by Winkler desc
  ) where rownum<=1 -- TOP N rows
    and Winkler>=95 -- minimum match %
;