title: Oracle- need to use CONTAINS to find a string that contains a period
body: <p>I have a field in a table that is domain indexed so I can use CONTAINS in a query.  I am trying to create a query that can find an acronym (i.e. J.A.V.A.) exactly.  Problem is that the period '.' is a stopword that gets ignored.  I need to figure out how to do this by escaping the '.' somehow.  I can't find any reference online about how to do this, or maybe I am not fully understanding what I am reading.  </p>
  
  <p>I have tried</p>
  
  <pre><code>SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A}')&gt;0;
  
  SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J_A_V_A_}')&gt;0;
  
  SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J\.A\.V\.A\.}')&gt;0;
  </code></pre>
  
  <p>I created the domain index by:
      CREATE INDEX table_txt_idx ON table(CLOB_column) INDEXTYPE IS CTXSYS.CONTEXT;</p>
  
  <p>I saw something (<a href="https://oracle-base.com/articles/9i/full-text-indexing-using-oracle-text-9i" rel="nofollow noreferrer">https://oracle-base.com/articles/9i/full-text-indexing-using-oracle-text-9i</a>) (<a href="https://docs.oracle.com/database/121/CCREF/csql.htm#CCREF0100" rel="nofollow noreferrer">https://docs.oracle.com/database/121/CCREF/csql.htm#CCREF0100</a>) about appending to the index but I don't think it applies to a ctxsys index, but I am not fully sure.  I just know what I used didn't work.</p>
  
  <pre><code>BEGIN
    CTX_DLL.OPTIMIZE_INDEX('IDX_COLUMN_TXT','FAST');
  END;
  </code></pre>
  
  <p>This didn't mention the ctxsys (<a href="https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD" rel="nofollow noreferrer">https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD</a>)</p>
  
  <p>I have created similar queries using LIKE and REGEXP_INSTR and need to create the same using CONTAINS so I can make sure I run the one that works the quickest.  </p>
  
  <pre><code>SELECT * from table WHERE CLOB_column LIKE '%J.A.V.A.%';  --4.441 seconds
  
  SELECT * from table WHERE REGEXP_INSTR(CLOB_column, '(J\.A\.V\.A\.)')&gt;0; --23.528 seconds
  </code></pre>