Skip to Main Content


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


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


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

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;

---- ---- ----
M560 M563 M563

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

Hope this helps.

UPDATE 21 June 2024

Oracle Database 23ai added a native FUZZY_MATCH operator in SQL


  (1 rating)

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


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