Perform text search queries

To perform a text search, you use the etx_contains() operator in the WHERE clause of a SELECT statement.

For example, suppose that you store your search text in a CLOB column named abstracts. To execute a pattern search on this column for the phrase multimedia document editor, execute the following statement:

SELECT title FROM reports
    WHERE etx_contains(abstract, 
    Row('multimedia document editor', 
        'SEARCH_TYPE = PHRASE_EXACT & PATTERN_ALL'));

The search returns the title column of the documents that contain the phrases mulitmedia document editor,

multimidia
ducument editer

, and even

multimillion documentary
editorials

, although the last hit has a much lower score than the first two hits. Since the statement specified an exact phrase search, the search does not find documents that contain just the word multimedia or the phrase editorial of a multimedia event because of the differing order of the words multimedia and editorial.

Important: Different character sets support different numbers of characters in an index. For a character set that supports more characters, fewer characters are searched in a clue word, and vice versa. See Size of character sets and searching clue words for more information.

Alternatively, you can use the WILDCARD_CLUE query parameter to perform a wildcard search. In this case, the clue can have an * character at the beginning, end (or both) of any or all words in the clue, and the * is expanded to match any string of characters, as in this query:

SELECT title FROM reports
   WHERE etx_contains(abstract,
   Row("*forgiv* drink*", "WILDCARD_CLUE & search_type=PHRASE_EXACT"));

This query finds unforgivable drinking and forgiven drinkers. The query does not find unforgiving nondrinkers, since there is no * at the beginning of the clue word drink*.

Use a keyword proximity search to find documents that contain either of the phrases multimedia editor or

editor
of a multimedia event

. The preceding example shows that phrase searching might not be your best choice, since the order of the words always counts in phrase searches. Order does not count in keyword searches because the words are treated as separate entities. Proximity searching ensures that the keywords are close to each other. The following is an example of a keyword proximity search:

SELECT title FROM reports
    WHERE etx_contains(abstract, 
    Row('multimedia editor', 
        'SEARCH_TYPE = PROX_SEARCH(5) '));

The search returns the title column of documents that contain both the keywords multimedia and editor as long as they are no more than five words apart, inclusive. This means that the search does not return a document that contains the phrase editor of a world class magazine known for its cutting edge articles on multimedia because the keywords multimedia and editor are separated by more than five words.

Sometimes it is necessary to search for stopwords because they are relevant parts of the clue. For example, you might want to search for the exact phrase plug and play where the word and is a stopword. The text search engine by default does not search for stopwords, so the result of the search might not be exactly what you want. The following example shows how you can force the inclusion of stopwords in a search:

SELECT title FROM reports
    WHERE etx_contains(abstract, 
    Row('plug and play',
        'SEARCH_TYPE = PHRASE_EXACT & CONSIDER_STOPWORDS'));

Important: You must specify the index parameter INCLUDE_STOPWORDS when you create the etx index if you want to use the tuning parameter CONSIDER_STOPWORDS. The index parameter INCLUDE_STOPWORDS forces the stopwords specified by the STOPWORD_LIST index parameter to be indexed; by default, the stopwords specified by this parameter are not indexed.