VistaDB 5
Query a full text index
Performing a lookup within an index requires that you specific which column you wish to use for the lookup (or * for all included in the FTS index), and the pattern you are searching for within that text.

CONTAINS can search for:

  • A word
  • The start of a word

Note that we do not support the following types of CONTAINS operations at this time:

  • Exact phrase matching
  • A word near another word
  • A word stemmed from another word (the word drive might respond to drives, drove, driven, driving)
  • A synonym of another word using a thesaurus (the word happy might match gay, excited, joyous, etc)
  • Term weights for specifying more important words in a search


SELECT * FROM table WHERE CONTAINS( COLUMN(S), 'pattern')

COLUMN(S)
The column name to be searched, or more than one column separated by , to indicate inclusion. They must have been included in the index when it was built.
Optionally the term * may be used to mean "search all columns included in the index".

Single Word Matches
The word to be matched. Only SINGLE word matches are supported in current engine. You cannot include AND, OR, etc at this time.

Example:

select * from mytable where CONTAINS(*, 'word')

Prefix term for wildcard matching
Specifies a match of words that start with the characters included up to the * symbol. The asterisk matches zero, one, or more characters. Only single word patterns are supported.

Examples:

select * from mytable where CONTAINS(*, '"word*"')

word* would match: word, wording, worded, wordsmith

select * from mytable where CONTAINS(*, '"test*"')

The above would match for test, testing, tested, etc.
See Also