4 Replies Latest reply: Jan 14, 2013 1:32 PM by kevinUCB RSS

    Contains Fuzzy name list

    user590292
      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
          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
            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
              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
                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].