Types of Full Text Searches

Using full text search, you can search for terms, prefixes, or phrases (sequences of terms). You can also combine multiple terms, phrases, or prefixes into Boolean expressions, or use proximity searches to require that expressions appear near to each other.

Perform a full text search using a CONTAINS clause in either a WHERE clause, or a FROM clause of a SELECT statement.

• Term and phrase search
  When performing a full text search for a list of terms, the order of terms is not important unless they are within a phrase. If you put the terms within a phrase, the database server looks for those terms in exactly the same order, and same relative positions, in which you specified them.
• Prefix search
  The full text search feature allows you to search for the beginning portion of a term. This is called a prefix search. To perform a prefix search, you specify the prefix you want to search for, followed by an asterisk. This is called a prefix term.
• Proximity search
  The full text search feature allows you to search for terms that are near each other in a single column. This is called a proximity search. To perform a proximity search, you specify two terms with either the keyword NEAR or BEFORE between them, or the tilde (~).
• Boolean search
  You can specify multiple terms separated by Boolean operators when performing full text searches. SAP Sybase IQ supports the following Boolean operators when performing a full text search: AND, OR, and AND NOT.
• Fuzzy search
  Fuzzy searching can be used to search for misspellings or variations of a word. To do so, use the FUZZY operator followed by a string in double quotes to find an approximate match for the string. For example, CONTAINS ( Products.Description, 'FUZZY "cotton"' ) returns cotton and misspellings such as coton or cotten.
• View search
  To use a full text search on a view or derived table, you must build a text index on the columns in the base table that you want to perform a full text search on. The following statements create a view on the MarketingInformation table in the sample database, which already has a text index name, and then perform a full text search on that view.