3 Replies Latest reply: Jan 8, 2013 10:07 AM by skv RSS

    How to get data for exact word match - Please help

    skv
      Hi,

      I have created Oracle Text index on one column, say col2. I want to get the data that exactly match word in the given criteria (i.e., "P.A.C.") and I have written below two queries


      SELECT col1, col2, col3
      FROM table1
      WHERE CONTAINS (col2, '{P.A.C.}') > 0;


      SELECT col1, col2, col3
      FROM table1
      WHERE CONTAINS (col2, '{"P.A.C."}') > 0;


      Above two queries are fetching same data like below. But I want only one record from below list, i.e., P.A.C. MEETING.


      P/E RATIO
      I/P SUMMARY
      P. BRADFORD
      P FALL
      P.A.C. MEETING



      Can you please help me how to achieve this. I am working on Oracle 11g.

      Thanks
        • 1. Re: How to get data for exact word match - Please help
          Barbara Boehmer
          Please provide create table and insert and create index statements and queries to reproduce the case and a copy and paste of a run of your code, similar to the code that I have run below, which shows different results from what you are describing.
          SCOTT@orcl_11gR2> SELECT * FROM v$version
            2  /
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          PL/SQL Release 11.2.0.1.0 - Production
          CORE     11.2.0.1.0     Production
          TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
          NLSRTL Version 11.2.0.1.0 - Production
          
          5 rows selected.
          
          SCOTT@orcl_11gR2> CREATE TABLE table1
            2    (col2  VARCHAR2(30))
            3  /
          
          Table created.
          
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO table1 VALUES ('P/E RATIO')
            3  INTO table1 VALUES ('I/P SUMMARY')
            4  INTO table1 VALUES ('P. BRADFORD')
            5  INTO table1 VALUES ('P FALL')
            6  INTO table1 VALUES ('P.A.C. MEETING')
            7  SELECT * FROM DUAL
            8  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> CREATE INDEX table1_col2_idx
            2  ON table1 (col2)
            3  INDEXTYPE IS CTXSYS.CONTEXT
            4  /
          
          Index created.
          
          SCOTT@orcl_11gR2> SELECT col2 FROM table1
            2  WHERE  CONTAINS (col2, '{P.A.C.}') > 0
            3  /
          
          COL2
          ------------------------------
          P.A.C. MEETING
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT col2 FROM table1
            2  WHERE  CONTAINS (col2, '{"P.A.C."}') > 0
            3  /
          
          COL2
          ------------------------------
          P.A.C. MEETING
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT col2 FROM table1
            2  WHERE  CONTAINS (col2, '"P.A.C."') > 0
            3  /
          
          COL2
          ------------------------------
          P.A.C. MEETING
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT col2 FROM table1
            2  WHERE  CONTAINS (col2, 'P.A.C.') > 0
            3  /
          
          COL2
          ------------------------------
          P.A.C. MEETING
          
          1 row selected.
          • 2. Re: How to get data for exact word match - Please help
            Barbara Boehmer
            I will take a guess at what problems you are likely encountering. If you do not specify a stoplist, then Oracle Text uses the default_stoplist, which includes the word A, so A is not indexed. You can change this by using an empty_stoplist or one that does not have A as a stopword. Also, by default the period is not indexed, unless you specify it in the printjoins parameter of a lexer. Please see the demonstration below that includes these things, showing the differences with and without them.
            SCOTT@orcl_11gR2> SELECT * FROM v$version
              2  /
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE     11.2.0.1.0     Production
            TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production
            
            5 rows selected.
            
            SCOTT@orcl_11gR2> CREATE TABLE table1
              2    (col2  VARCHAR2(30))
              3  /
            
            Table created.
            
            SCOTT@orcl_11gR2> INSERT ALL
              2  INTO table1 VALUES ('P/E RATIO')
              3  INTO table1 VALUES ('I/P SUMMARY')
              4  INTO table1 VALUES ('P. BRADFORD')
              5  INTO table1 VALUES ('P FALL')
              6  INTO table1 VALUES ('P.A.C. MEETING')
              7  INTO table1 VALUES ('P.B.C.')
              8  INTO table1 VALUES ('P A C')
              9  SELECT * FROM DUAL
             10  /
            
            7 rows created.
            
            SCOTT@orcl_11gR2> -- without lexer and with default stoplist:
            SCOTT@orcl_11gR2> CREATE INDEX table1_col2_idx
              2  ON table1 (col2)
              3  INDEXTYPE IS CTXSYS.CONTEXT
              4  /
            
            Index created.
            
            SCOTT@orcl_11gR2> SELECT token_text FROM dr$table1_col2_idx$i
              2  /
            
            TOKEN_TEXT
            ----------------------------------------------------------------
            B
            BRADFORD
            C
            E
            FALL
            MEETING
            P
            RATIO
            SUMMARY
            
            9 rows selected.
            
            SCOTT@orcl_11gR2> SELECT col2 FROM table1
              2  WHERE  CONTAINS (col2, 'P.A.C.') > 0
              3  /
            
            COL2
            ------------------------------
            P.A.C. MEETING
            P.B.C.
            P A C
            
            3 rows selected.
            
            SCOTT@orcl_11gR2> -- with lexer and with empty stoplist:
            SCOTT@orcl_11gR2> DROP INDEX table1_col2_idx
              2  /
            
            Index dropped.
            
            SCOTT@orcl_11gR2> BEGIN
              2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
              3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '.');
              4  END;
              5  /
            
            PL/SQL procedure successfully completed.
            
            SCOTT@orcl_11gR2> CREATE INDEX table1_col2_idx
              2  ON table1 (col2)
              3  INDEXTYPE IS CTXSYS.CONTEXT
              4  PARAMETERS
              5    ('STOPLIST CTXSYS.EMPTY_STOPLIST
              6        LEXER       test_lex')
              7  /
            
            Index created.
            
            SCOTT@orcl_11gR2> SELECT token_text FROM dr$table1_col2_idx$i
              2  /
            
            TOKEN_TEXT
            ----------------------------------------------------------------
            A
            BRADFORD
            C
            E
            FALL
            I
            MEETING
            P
            P.A.C
            P.B.C
            RATIO
            SUMMARY
            
            12 rows selected.
            
            SCOTT@orcl_11gR2> SELECT col2 FROM table1
              2  WHERE  CONTAINS (col2, 'P.A.C.') > 0
              3  /
            
            COL2
            ------------------------------
            P.A.C. MEETING
            
            1 row selected.
            • 3. Re: How to get data for exact word match - Please help
              skv
              Thank you so much Barbara. Your guess solved my problem.