This discussion is archived
3 Replies Latest reply: Oct 30, 2012 1:07 AM by 971231 RSS

How can I find out what word fuzzy search matched

971231 Newbie
Currently Being Moderated
I tried to find something relating to my problem but search resulted no relevant threads. I'm not using fuzzy to search text, but names and I use it mainly to show results even though user misspelled the name of a resource.

In order to be able to tune the search I would like to know what word the fuzzy search actually matched and what was the score for that match.

So if I search for "sumer" I get hit for "warm summer" and the relation is clear. But if I get "soon swimming is not possible as summit closes" the hit is not evident anymore.
  • 1. Re: How can I find out what word fuzzy search matched
    Roger Ford Expert
    Currently Being Moderated
    I'm not too sure what you're asking here. Can you post your index creation script and the query you're running so we can get a better idea?
  • 2. Re: How can I find out what word fuzzy search matched
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    You can use fuzzy with weight and use the score function to get the score. You can use ctx_doc.highlight to get the positions (offsets and length) of the matching words, then use that in a pipelined function to return those words and use that function in your query. Please see the demonstration below.

    -- table, data, and index:
    SCOTT@orcl_11gR2> CREATE TABLE test_tab
      2    (test_col  VARCHAR2(46))
      3  /
    Table created.
    SCOTT@orcl_11gR2> INSERT ALL
      2  INTO test_tab VALUES ('warm summer')
      3  INTO test_tab VALUES ('soon swimming is not possible as summit closes')
      4  INTO test_tab VALUES ('other data')
      6  /
    3 rows created.
    SCOTT@orcl_11gR2> CREATE INDEX test_idx
      2  ON test_tab (test_col)
      4  /
    Index created.
    -- function
    SCOTT@orcl_11gR2> create or replace function get_words
      2    (p_idx          in varchar2,
      3       p_rid          in rowid,
      4       p_query      in varchar2,
      5       p_col          in varchar2)
      6    return          sys.odcivarchar2list
      7    authid          definer
      8    pipelined
      9  as
     10    v_hightab     ctx_doc.highlight_tab;
     11  begin
     12    ctx_doc.set_key_type ('rowid');
     13    ctx_doc.highlight (p_idx, p_rid, p_query, v_hightab);
     14    for i in 1 .. v_hightab.count loop
     15        pipe row
     16          (dbms_lob.substr
     17            (p_col,
     18             v_hightab(i).length,
     19             v_hightab(i).offset));
     20    end loop;
     21    return;
     22  end get_words;
     23  /
    Function created.
    SCOTT@orcl_11gR2> show errors
    No errors.
    -- search:
    SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100)
    SCOTT@orcl_11gR2> EXEC :search_string := 'sumer'
    PL/SQL procedure successfully completed.
    SCOTT@orcl_11gR2> COLUMN words FORMAT A15
    SCOTT@orcl_11gR2> SELECT SCORE(1), x.column_value words, t.test_col
      2  FROM   test_tab t,
      3           TABLE
      4             (get_words
      5            ('test_idx',
      6             t.ROWID,
      7             'FUZZY (?' || :search_string || ', 1, 5000, W)',
      8             t.test_col)) x
     10             (t.test_col,
     11              'FUZZY (?' || :search_string || ', 1, 5000, W)',
     12              1) > 0
     13  ORDER  BY SCORE(1) DESC
     14  /
      SCORE(1) WORDS           TEST_COL
    ---------- --------------- ----------------------------------------------
            36 summer          warm summer
            12 summit          soon swimming is not possible as summit closes
    2 rows selected.
  • 3. Re: How can I find out what word fuzzy search matched
    971231 Newbie
    Currently Being Moderated
    Thanks Barbara,

    I'll try to incorporate that to my script.


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