This discussion is archived
3 Replies Latest reply: Jan 8, 2013 8:07 AM by skv RSS

How to get data for exact word match - Please help

skv Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you so much Barbara. Your guess solved my problem.

Legend

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