You can combine the predicates with any of the other Transact-SQL predicates, such as LIKE and BETWEEN. Matching rows are returned in the result set. The CONTAINS and FREETEXT predicates return a TRUE or FALSE value that indicates whether a given row matches the full-text query. Use the full-text predicates CONTAINS and FREETEXT in the WHERE or HAVING clause of a SELECT statement. The following info helps you to choose the best predicate or function for your query: Predicates CONTAINS and FREETEXT The predicates CONTAINS/ FREETEXT and the rowset-valued functions CONTAINSTABLE/ FREETEXTTABLE have different syntax and options. Matches are generated if any term or form of any term is found in the full-text index of a specified column.Match the meaning, but not the exact wording, of specified words, phrases, or sentences (the freetext string).For more info, see Using Boolean operators (AND, OR, and NOT) later in this article. Combine search conditions with logical operators.Specify the proximity of words within a certain distance of one another.Match single words and phrases with precise or fuzzy (less precise) matching.The following info helps you to choose the best predicate or function for your query: CONTAINS/CONTAINSTABLE Here is an extension of the same query that only returns rows with a rank of 10 or greater: USE AdventureWorks2012ĬONTAINS/ CONTAINSTABLE and FREETEXT/ FREETEXTTABLE are useful for different kinds of matching. USE AdventureWorks2012įROM Production.ProductDescription AS FT_TBLįREETEXTTABLE(Production.ProductDescription, Description, To write a similar query, you have to know that ProductDescriptionID is the unique key column for the ProductDescription table. The following example extends a FREETEXTTABLE query to return the highest ranked rows first and to add the ranking of each row to the select list. ON FT_TBL.ProductDescriptionID = KEY_TBL. USE AdventureWorks2012įROM Production.ProductDescription AS FT_TBL INNER JOINĬONTAINSTABLE (Production.ProductDescription, The following example returns the description ID and description of all products for which the Description column contains the word "aluminum" near either the word "light" or the word "lightweight." Only rows with a rank of 2 or higher are returned. WHERE FREETEXT (Document, 'vital safety components') The following example searches for all documents that contain words related to vital safety components: USE AdventureWorks2012 The following example finds all products with a price of $80.99 that contain the word "Mountain": USE AdventureWorks2012 For more info, see Get Started with Full-Text Search. To run the sample queries, you also have to set up Full-Text Search. For the final release of AdventureWorks, see AdventureWorks Databases and Scripts for SQL Server 2016 CTP3. The following examples use the AdventureWorks sample database.
To match the meaning, but not the exact wording, use FREETEXT and FREETEXTTABLE.To match words and phrases, use CONTAINS and CONTAINSTABLE.This article provides examples of each predicate and function and helps you choose the best one to use. Write full-text queries by using the predicates CONTAINS and FREETEXT and the rowset-valued functions CONTAINSTABLE and FREETEXTTABLE with a SELECT statement. Applies to: SQL Server (all supported versions) Azure SQL Database
0 kommentar(er)
