OraFAQ Forum: Text & interMedia » phonetically search

Home » Server Options » Text & interMedia » phonetically search (10g 10.2.0.0, windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

phonetically search [message #416682]

Mon, 03 August 2009 22:48

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

Report message to a moderator

Re: phonetically search [message #416684 is a reply to message #416682]

Mon, 03 August 2009 23:01

Michel Cadot
Messages: 67842
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

Report message to a moderator

Re: phonetically search [message #416714 is a reply to message #416682]

Tue, 04 August 2009 01:18

Barbara Boehmer
Messages: 8922
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>

Report message to a moderator

Re: phonetically search [message #416777 is a reply to message #416714]

Tue, 04 August 2009 05:04

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

Report message to a moderator

Previous Topic:	Oracle BLOB PDF Text Search Question
Next Topic:	ora-29952 when split partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 09 06:37:57 CDT 2021