Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Zahir.

Asked: November 07, 2017 - 7:44 pm UTC

Last updated: June 21, 2024 - 10:02 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Is there any SQL construct that does fuzzy matching ?

As an example , if I have the values as Monroe , Monroe Twp , Monroe Township , "Monroe Twp,NJ" , I would like to consider them as one value .






and Connor said...

Some options you can consider to map words to a common value:

SOUNDEX function

https://docs.oracle.com/database/122/SQLRF/SOUNDEX.htm#SQLRF06109

UTL_MATCH

https://docs.oracle.com/database/122/ARPLS/UTL_MATCH.htm#ARPLS352

and there is an open source PL/SQL version of double metaphone on github

https://github.com/AliArdaOrhan/Double_Metaphone


Each have pros and cons, eg SOUNDEX matches 2 but not 3 of your candidate values

SQL> select
  2    soundex('Monroe'),
  3    soundex('Monroe Twp') ,
  4    soundex('Monroe Township')
  5  from dual;

SOUN SOUN SOUN
---- ---- ----
M560 M563 M563


If you are looking at *searching* for information, then we have fuzzy text matching in Oracle Text

https://docs.oracle.com/database/122/CCAPP/indexing-with-oracle-text.htm#CCAPP9071

Hope this helps.

UPDATE 21 June 2024

Oracle Database 23ai added a native FUZZY_MATCH operator in SQL

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/data-quality-operators.html#GUID-C13A179C-1F82-4522-98AA-E21C6504755E

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Answer out of date

SQLWitch, June 20, 2024 - 9:04 pm UTC

Chris Saxon
June 21, 2024 - 10:02 am UTC

Good point; we've updated this