OraFAQ Forum: Text & interMedia » phonetically search

There are various methods and algorithms that you can use. You can use the Oracle supplied soundex function within a sql statement to just return values that are similar. Or you can use one of the Oracle supplied jaro winkler functions to rank how similar the names are and establish a cut-off based on score or rows. Or, you can use the ! operator within an Oracle Text search to obtain soundex results. There are also various other algorithms, such as metaphone. The jaro winkler functions were created specifically for phonetic name comparisons, whereas soundex is more generally for all words. Please see the demo below.

-- test environment:

SCOTT@orcl_11g> CREATE TABLE your_table
  2    (name  VARCHAR2 (30))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO your_table VALUES ('MOHAMAD')
  3  INTO your_table VALUES ('MUHAMMAD')
  4  INTO your_table VALUES ('MAHAMMAD')
  5  INTO your_table VALUES ('MAHHAMMAD')
  6  INTO your_table VALUES ('SOMEBODY')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

-- soundex:

SCOTT@orcl_11g> SELECT *
  2  FROM   your_table
  3  WHERE  SOUNDEX ('MOHAMMAD') = SOUNDEX (name)
  4  /

NAME
------------------------------
MOHAMAD
MUHAMMAD
MAHAMMAD
MAHHAMMAD

-- jaro winkler:

SCOTT@orcl_11g> SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY ('MOHAMMAD', name) jws,
  2  	    name
  3  FROM   your_table
  4  ORDER  BY jws DESC
  5  /

       JWS NAME
---------- ------------------------------
        97 MOHAMAD
        92 MUHAMMAD
        83 MAHAMMAD
        81 MAHHAMMAD
        47 SOMEBODY

-- Oracle Text search using soundex operator (!):

SCOTT@orcl_11g> CREATE INDEX your_idx ON your_table (name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT name
  2  FROM   your_table
  3  WHERE  CONTAINS (name, '!MOHAMMAD') > 0
  4  /

NAME
------------------------------
MOHAMAD
MUHAMMAD
MAHAMMAD
MAHHAMMAD

SCOTT@orcl_11g>