3 Replies Latest reply: Oct 30, 2012 3:07 AM by 971231 RSS

    How can I find out what word fuzzy search matched

    971231
      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-Oracle
          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
            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')
              5  SELECT * FROM DUAL
              6  /
            
            3 rows created.
            
            SCOTT@orcl_11gR2> CREATE INDEX test_idx
              2  ON test_tab (test_col)
              3  INDEXTYPE IS CTXSYS.CONTEXT
              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
              9  WHERE  CONTAINS
             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
              Thanks Barbara,

              I'll try to incorporate that to my script.