How can I get ORACLE CONTAINS to match the literal word 'NOT'

Question

Why does this

SELECT * FROM clinical_trials
WHERE CONTAINS(study_title, '%Not%') > 0

not match: 'Not Available'

Answer 1

Check the stop list you use for the text index. The default - DEFAULT_STOPLIST - includes 'not' so this word is not indexed by default.

You can see the defined stop lists and their content using

SELECT * FROM ctxsys.ctx_stopwords;

and

SELECT * FROM ctxsys.ctx_stoplists;