This discussion is archived
4 Replies Latest reply: Jan 14, 2013 11:32 AM by kevinUCB RSS

Contains Fuzzy name list

user590292 Newbie
Currently Being Moderated
Hi, When I run my query

select score(1), ae.pref_mail_name
from address_extract ae
where contains(pref_mail_name,'fuzzy(raleigh,,,weight)',1) > 0
order by score(1) desc

I get results as expected

100     Mr. Raleigh H. Smith
100     Mr. Nicolas Balcom Raleigh
100     Ms. Ronald M. Raleigh
66     Mrs. LaReign Smith
66     Ms. Rahil Smith
62     Ms. Sam S. Ragalie
62     Mr. Sue S. Ragalie

But I cannot successfully add 'Nick' to the fuzzy search

select score(1), ae.pref_mail_name
from address_extract ae
where contains(pref_mail_name,'fuzzy(raleigh,,,weight) and fuzzy(nick,,,weight)',1) > 0
order by score(1) desc

returns no results

Other expected fuzzy searches fail too

"Tommy" does not find any "Thomas"
"Sara" does not find any "Sarah"

Any help would be great.
  • 1. Re: Contains Fuzzy name list
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    You might try changing the parameters, for example:

    raleigh, 1, 5000, weight

    For things like Tommy and Thomas, you might try creating synonyms for them.
  • 2. Re: Contains Fuzzy name list
    user590292 Newbie
    Currently Being Moderated
    Well, the raleigh part is not the issue. I thought that the fuzzy search would do more name variants. Maybe I don't understand your suggestion, can I define custom synonyms for the fuzzy search?
  • 3. Re: Contains Fuzzy name list
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    There are four parameters for fuzzy. The first is the term to search for. The second is the minimum score. If you do not specify a minimum score, then the default is 60. So, when you use "someword, , , weight" it only returns rows that score 60 or above. If you use "someword, 1, 5000, weight" then it returns rows that score 1 or above, which will return more results, that don't match as closely. This might solve your problem when adding a search for nick or sara or whatever.

    Even with the lower score, some things may still not be found, like Tommy and Thomas. Fuzzy searches for similar spellings. You can create a custom thesaurus and enter synonyms for such nicknames, then use SYN within your CONTAINS to search for them and find them.
  • 4. Re: Contains Fuzzy name list
    kevinUCB Explorer
    Currently Being Moderated
    If pref_mail_name is a list of people's names, you should at least experiment with defining the column as an NDATA section, then using the NDATA operator [http://docs.oracle.com/cd/E11882_01/text.112/e24436/cqoper.htm#CCHDHHBE].

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points