CODEWITHSUNDEEP
oracle-databaseselectcontainsclob
Jennifer Crosby
Jennifer Crosby

Reputation: 323

searching in CLOB for words in a list/table

I have a large table with a clob column (+100,000 rows) from which I need to search for specific words within a certain timeframe. 

{select id, clob_field,  dbms_lob.instr(clob_field, '.doc',1,1) as doc,  --ideally want .doc
      dbms_lob.instr(clob_field, '.docx',1,1) as docx, --ideally want .docx
      dbms_lob.instr(clob_field, '.DOC',1,1) as DOC,  --ideally want .DOC
      dbms_lob.instr(clob_field, '.DOCX',1,1) as DOCX  --ideally want .DOCX
 from clob_table, search_words s
 where (to_char(date_entered, 'DD-MON-YYYY') 
      between to_date('01-SEP-2018') and to_date('30-SEP-2018'))
 AND (contains(clob_field, s.words )>0)  ;}

The set of words are '.doc', '.DOC', '.docx', and '.docx'. When I use CONTAINS() it seems to ignore the dot and so provides me with lots of rows, but not with the document extensions in it. It finds emails with .doc as part of the address, so the doc will have a period on either side of it.

i.e. [email protected]

I don't want those occurrences. I have tried it with a space at the end of the word and it ignores the spaces. I have put these in a search table I created, as shown above, and it still ignores the spaces. Any suggestions?

Thanks!!

Upvotes: 1

Views: 821

Answers (1)

kfinity
kfinity

Reputation: 9091

Here's two suggestions.

The simple, inefficient way is to use something besides CONTAINS. Context indexes are notoriously tricky to get right. So instead of the last line, you could do:

AND regexp_instr(clob_field, '\.docx', 1,1,0,'i') > 0

I think that should work, but it might be very slow. Which is when you'd use an index. But Oracle Text indexes are more complicated than normal indexes. This old doc explains that punctuation characters (as defined in the index parameters) are not indexed, because the point of Oracle Text is to index words. If you want special characters to be indexed as part of the word, you need to add it to the set of printjoin characters. This doc explains how, but I'll paste it here. You need to drop your existing CONTEXT index and re-create it with this preference:

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'printjoins', '._-'); -- periods, underscores, dashes can be parts of words
end;
/

CREATE INDEX myindex on clob_table(clob_field) INDEXTYPE IS CTXSYS.CONTEXT
  parameters ('LEXER mylex');

Keep in mind that CONTEXT indexes are case-insensitive by default; I think that's what you want, but FYI you can change it by setting the 'mixed_case' attribute to 'Y' on the lexer, right below where you set the printjoins attribute above.

Also it seems like you're trying to search for words which end in .docx, but CONTAINS isn't INSTR - by default it matches entire words, not strings of characters. You'd probably want to modify your query to do AND contains(clob_field, '%.docx')>0

Upvotes: 1

Related Questions