20.3.3. Text Expression Syntax
expr ::= proximity_expr expr AND expr | expr OR expr | expr AND NOT expr | '(' expr ')' word_expr ::= word | '"' phrase '"' proximity_expr ::= word_expr | proximity_expr NEAR word_expr word ::= <word char>* phrase ::= word | phrase <whitespace> word word_char ::= alphanumeric characters, '*', ISO Latin accented characters.
A word is a sequence of word characters. A phrase is a sequence of words separated by white spaces and enclosed in double quotes. If a word contains a wildcard character it must be quoted with double quotes.
Note: | |
---|---|
An expression may not consist of all negative terms, e.g. (not a) and (not b) is not a valid expression but 'c and not a and not b' is a valid expression. Note that the NEAR connective may not be used between AND'ed or OR'ed terms. It can be used to combine words or phrases. |
Example 20.4. Querying Free Text Indexed Columns
select count (*) from docs where contains (text, '"virtual database"')
returns the count of documents with one or more occurrences of "virtual" immediately followed by "database".
'performance and (tuning or optimization)'
specifies documents with performance and either 'tuning' or optimization' in any respective positions.
'graphics and not (graphics near user near interface)'
matches documents with the word graphics more than 100 words away from 'user' or 'interface'.
'"sql interfac*"'
matches documents with SQL followed by a word beginning with 'interfac'.
'"dragon*" and not "once upon a time"'
matches documents with words beginning with 'dragon' and not containing the phrase 'once upon a time'.