full-text search examples

原创 2012年03月24日 23:53:37

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:

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]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC
GO


 


B. 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.

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



 

相关文章推荐

维多利亚大学Full-Text Search PPT教材

  • 2011年09月14日 05:25
  • 2.04MB
  • 下载

PostgreSQL 8.3.1 全文检索(Full Text Search)

原文出自:http://blog.csdn.net/hbzy0215/archive/2009/03/17/3998402.aspx保存以供学习,谢谢原创作者  PostgreSQL 8.3.1  全...
  • CYXLZZS
  • CYXLZZS
  • 2011年02月22日 11:56
  • 1044

iOS/Android SQLite 全文检索——FTS (Full Text Search)

前言我们的APP部分功能为了满足用户离线使用搜索的场景,使用了内置SQLite数据库的方式,随着内容的日益丰富,数据库记录快速增多,导致搜索速度明显变慢,为了提升搜索速度,给我们的数据做了全文检索的支...

elasticsearch---search in depth之full-text search

full-text中两个关键因素:analyzed和relevence

Finding vulnerabilities in PHP scripts FULL ( with examples )

Name : Finding vulnerabilities in PHP scripts FULL ( with examples ) Author : SirGod Email : sirgod...
  • yatere
  • yatere
  • 2011年11月16日 16:02
  • 903

VI/VIM text editor 12 Powerful Find and Replace Examples

In this article, let us review how to perform both basic and advanced text and pattern substitution ...

【每周一文】Building Text Classifiers Using Positive and Unlabeled Examples(2003)

概述 PULearning是一类机器学习算法,主要解决的问题是给定训练样本只有正样本和一批未标记的样本,学习一个分类器进行分类。这是一类比较难解决的问题,由于没有负样本其评估的方法不好确定;但是有很...

30 Examples For Awk Command In Text Processing

In the previous post, we talked about sed command and we saw many examples of using it in text pro...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:full-text search examples
举报原因:
原因补充:

(最多只允许输入30个字)