full-text search examples

最新推荐文章于 2020-08-29 03:00:41 发布

最新推荐文章于 2020-08-29 03:00:41 发布 · 126 阅读

文章标签：

本文详细介绍了SQL中使用CONTAINS、FREETEXT、CONTAINSTABLE和FREETEXTTABLE进行全文搜索和近似搜索的方法。通过具体实例展示了如何在产品名称中查找特定价格的产品、使用FREETEXT搜索包含特定关键词的文档、使用CONTAINSTABLE进行复杂关键字的搜索以及使用FREETEXTTABLE进行高级查询并添加排名。

A. Using CONTAINS with <simple_term>

The following example finds all products with a price of $80.99 that contain the word "Mountain".

USE AdventureWorks2012 GO SELECT Name, ListPrice FROM Production.Product WHERE ListPrice = 80.99 AND CONTAINS(Name, 'Mountain') GO

B. Using FREETEXT to search for words containing specified character values

The following example searches for all documents containing the words related to vital, safety, components.

USE AdventureWorks2012 GO SELECT Title FROM Production.Document WHERE FREETEXT (Document, 'vital safety components') GO

C. Using CONTAINSTABLE

The following example returns the description ID and description of all products for which the Description column contain the word "aluminum" near either the word "light" or the word "lightweight." Only rows with a rank value of 2 or higher are returned.

USE AdventureWorks2012 GO SELECT FT_TBL.ProductDescriptionID, FT_TBL.Description, KEY_TBL.RANK FROM Production.ProductDescription AS FT_TBL INNER JOIN CONTAINSTABLE (Production.ProductDescription, Description, '(light NEAR aluminum) OR (lightweight NEAR aluminum)' ) AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY] WHERE KEY_TBL.RANK > 2 ORDER BY KEY_TBL.RANK DESC; GO

D. Using FREETEXTTABLE

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. To specify the query, you must know that ProductDescriptionID is the unique key column for the ProductDescription table.

USE AdventureWorks2012 GO SELECT KEY_TBL.RANK, FT_TBL.Description FROM Production.ProductDescription AS FT_TBL INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description, 'perfect all-around bike') AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC GO

Here is an extension of the same query that only returns rows with a rank value of 10 or greater:Here is an extension of the same query that only returns rows with a rank value of 10 or greater:

B. Using FREETEXTTABLE

The following example uses the ProductDescription table of the AdventureWorks2012 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word "Aluminum" and the word "spindle." The search condition uses the AND Boolean operator.

USE AdventureWorks2012 GO SELECT Description FROM Production.ProductDescription WHERE ProductDescriptionID <> 5 AND CONTAINS(Description, 'aluminum AND spindle') GO