to search a string phonetically?

SCOTT@orcl_11g> CREATE TABLE your_table
2 (name VARCHAR2 (30))
3 / 4 INTO your_table VALUES ('MAHAMMAD')
5 INTO your_table VALUES ('MAHHAMMAD')
6 INTO your_table VALUES ('SOMEBODY')

Table created.

SCOTT@orcl_11g> INSERT ALL
2 INTO your_table VALUES ('MOHAMAD')
3 INTO your_table VALUES ('MUHAMMAD')

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