Home » Server Options » Text & interMedia » phonetically search (10g 10.2.0.0, windows xp)
phonetically search [message #416682] Mon, 03 August 2009 22:48 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears

Is it possible to search a string phonetically?

Suppose :-
I am searching the word :- MOHAMMAD

It should return like :- MOHAMMAD
MOHAMAD
MUHAMMAD
MAHAMMAD
MAHHAMMAD
........etc

please help me?


Best Regards
Halim
Re: phonetically search [message #416684 is a reply to message #416682] Mon, 03 August 2009 23:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at SOUNDEX function but it works only for english.
You can search for this on Google or AskTom.

Regards
Michel

Re: phonetically search [message #416714 is a reply to message #416682] Tue, 04 August 2009 01:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
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>

Re: phonetically search [message #416777 is a reply to message #416714] Tue, 04 August 2009 05:04 Go to previous message
halim
Messages: 100
Registered: September 2008
Senior Member

Many many thanks Barbara Boehmer and Michel cadet

soundex and Oracle Text search using soundex operator(!)
works nice .

I am surprized.

Best Regards
Muhammad Abdul Halim
Previous Topic: Oracle BLOB PDF Text Search Question
Next Topic: ora-29952 when split partition
Goto Forum:
  


Current Time: Thu Mar 28 13:18:55 CDT 2024