|
|
|
Description
The full-text predicate can be used to search text based on a full-text index. If an IN predicate specifies a query expression, then the result table it returns can contain only a single column. This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
Syntax
fulltext_predicate::
[ table_name. ] column MATCH phrase
phrase ::
token [ operator token [ operator token...] ... ]
operator ::
[ * | ^ | near | near/x | and | or ]
* wildcard match
^ search from beginning of column (only one ^ allowed per query)
near multiple near terms allowed
near/x specify number of tokens to be within the target search token. Multiple near terms allowed.
and logical AND operator
or logical OR operator
Example
Query all words from the “document” column from the “docs” table for which "linux" is the first token of at least one column:
SELECT * FROM docs WHERE document MATCH '^linux';
Query all documents for which the first token in column "title" begins with "lin":
SELECT * FROM docs WHERE body MATCH 'title: ^lin*';
Query all documents by phrase that contain a nominated set of terms or term prefixes in a specified order with no intervening tokens. Phrase queries are specified by enclosing a space-separated sequence of terms or term prefixes in double quotes ("). For example:
Query all documents that contain the phrase "linux applications":
SELECT * FROM docs WHERE document MATCH '"linux applications"';
Query all documents that contain a phrase that matches "lin* app*". This will match "linux applications" as well as common phrases such as "linoleum appliances" or "link apprentice".
SELECT * FROM docs WHERE document MATCH '"lin* app*"';
NOTE: See Planned Features for which operators are implemented.
