Supported Forms of Query Terms (Full-Text Search)
This topic introduces the forms of full-text queries supported by SQL Server, and summarizes the support provided for each form of query by the full-text predicates and rowset-valued functions. Full-text queries can be run only on columns that have been full-text indexed.
Query-term form |
Description |
Supported by |
|---|---|---|
One or more specific words or phrases (simple term) |
In full-text search, a word is considered to be a token. A token is identified by appropriate word breakers, following the linguistic rules of the specified language. A valid phrase can consist of multiple words, with or without punctuation between them. For example, "croissant" is a word, and "café au lait" is a phrase. Words and phrases such as these are called simple terms. For more information, see Searching for Specific Word or Phrase (Simple Term). |
CONTAINS and CONTAINSTABLE, which looks for an exact match for the phrase, and FREETEXT and FREETEXTTABLE, which break up the phrase into separate words. |
A word or a phrase where the words begin with specified text (prefix term) |
A prefix term refers to a string that is affixed to the front of a word to produce a derivative word or an inflected form. For a single prefix term, any word starting with the specified term will be part of the result set. For example, the term "auto*" matches "automatic", "automobile", and so forth. For a phrase, each word within the phrase is considered to be a prefix term. For example, the term "auto tran*" matches "automatic transmission" and "automobile transducer", but it does not match "automatic motor transmission". For more information, see Performing Prefix Searches. |
CONTAINS and CONTAINSTABLE |
Inflectional forms of a specific word (generation term) |
The inflectional forms are the different tenses of a verb or the singular and plural forms of a noun. For example, search for the inflectional form of the word "drive". If various rows in the table include the words "drive", "drives", "drove", "driving", and "driven", all would be in the result set because each of these can be inflectionally generated from the word drive. For more information, see Searching for the Inflectional Form of a Specific Word (Generation Term). |
FREETEXT and FREETEXTTABLE queries look for inflectional terms of all specified words by default. CONTAINS and CONTAINSTABLE support an optional INFLECTIONAL argument. |
A word or phrase close to another word or phrase (proximity term) |
A proximity term indicates words or phrases that are in close proximity to another word or phrase or two words or phrases in any order. For example, you want to find the rows in which the word "ice" is near the word "hockey" or in which the phrase "ice skating" is near the phrase "ice hockey". Whether two terms or phrases are considered to be near to each other is calculated internally and is not configurable by the user. Many data points are considered when calculating nearness. This type of query also returns documents or rows that contain the search terms, even if they are not near each other. For these results, the ranking is zero (0). For more information, see Searching for Words or Phrases Close to Another Word or Phrase (Proximity Term). |
CONTAINSTABLE .gif "Note") Note
FREETEXT and FREETEXTTABLE decipher the proximity syntax as searchable strings and do not give the expected results.
|
Synonymous forms of a specific word (thesaurus) |
A thesaurus defines user-specified synonyms for terms. For example, if an entry, "{car, automobile, truck, van}", is added to a thesaurus, you can search for the thesaurus form of the word "car". All rows in the table queried that include the words "automobile", "truck", "van", or "car", appear in the result set because each of these words belong to the synonym expansion set containing the word "car". For information about the structure of thesaurus files, see Thesaurus Configuration. |
FREETEXT and FREETEXTTABLE queries use the thesaurus by default. CONTAINS and CONTAINSTABLE support an optional THESAURUS argument. |
Words or phrases using weighted values (weighted term) |
A weighting value that indicates the degree of importance for each word and phrase within a set of words and phrases. A weight value of 0.0 is the lowest, and a weight value of 1.0 is the highest. For example, in a query searching for multiple terms, you can assign each search word a weight value indicating its importance relative to the other words in the search condition. The results for this type of query return the most relevant rows first, according to the relative weight you have assigned to search words. The result sets contain documents or rows containing any of the specified terms (or content between them); however, some results will be considered more relevant than others because of the variation in the weighted values associated with different searched terms. For more information, see Searching for Words or Phrases Using Weighted Values (Weighted Term). |
CONTAINSTABLE Note
Using CONTAINS or FREETEXT with weighted terms does not give relevancy to rows retrieved. FREETEXT and FREETEXTTABLE decipher the weights as searchable strings and do not give the expected results.
|