SQLServer 全文检索(full-text)语法

sql server 全文检索有两种搜索方式，一种是contains，另一种是freetext。前者是包含，类似于like '%关键词%'，后者则是将一段文字分词以后对每个词进行搜索。

具体语法：

contains：

　　SELECT 字段1,字段2

　　FROM 表名

　　WHERE contains(字段,'"词一" or "词二"')

　　根据查找结果的相似度排序

　　SELECT 字段1,字段2

　　FROM 表名

　　inner join containstable(表名, 字段,'"词一" or "词二"',10) as k

　　on 表名.id = k.[key]

　　order by k.RANK DESC

　　freetext:

　　SELECT 字段1,字段2

　　FROM 表名

　　WHERE freetext(字段,'词一词二')

　　根据查找结果的相似度排序

　　SELECT 字段1,字段2

　　FROM 表名

　　inner join freetexttable(表名, 字段,'词一词二',10) as k

　　on 表名.id = k.[key]

　　order by k.RANK DESC

上文中freetexttable或containstable的10表示取10条数据

具体详细操作：

数据库数据执行任意文本查询：

 1.使用FREETEXT谓词 FREETEXT接受两个参数。

第一个参数表示要搜索的列，可以提供列名，或者用*字符搜索表中的所有列。

第二个参数表示要搜索的短语。

例: select Title from Titles where FREETEXT(Title,'secret computer')

该SQL语句将数据库表Titles中的Title列中的内容包括secret或computer的纪录查询出来 。

 2.使用FREETEXTTABLE函数 FREETEXTTABLE接受三个参数。

第一个参数表示要搜索的表，

第二个参数表示要搜寻的列，

第三个参数表示要搜索的短语。它返回一个表，表中包含KEY和Rank两列，key表示与此匹 配相关联的纪录的惟一键。rank表示结果与查询的匹配程度，1000表示最佳，0表示最差。

例：select * from Freetexttable(Titles,Title,'a book about silicon valley') searchTable

此SQL语句返回表searchTable，表中有两列，key和rank。

 数据库数据的布尔查询：

 1.使用CONTAINS谓词

CONTAINS接受两个参数：要搜索的列和搜索短语。

1）搜索特定的词或短语（简单词）

例：select Title from Titles where contains(Title,'computer and not cooking')

此例返回title里包含computer的纪录不返回包含cooking的纪录，因为 and not。

如果短语中有词组

如：select student_id,student_name from students where CONTAINS( address, '"HEIBEI province"' )；也可以用CONTAINS( address, '"HEIBEI province"' )

2）执行前缀搜索（前缀词）

如果查询以‘hu’开头的地址：

SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"hu*"' )

这里是‘*’，而不是‘%’。

 3）搜索特定词的变形（派生词）

1SELECT Comments, ReviewerName
 FROM Production.ProductReview3
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')

4）使用加权值搜索词或短语（加权词）

SELECT AddressLine1, KEY_TBL.RANK 
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", 
         Street WEIGHT(0.9), 
         View WEIGHT(0.1)
         ) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

5）查询 varbinary(max) 和 xml 列

如果 varbinary(max)、varbinary 或 xml 列是全文索引列，则与任何其他全文索引列一样，可以使用全文谓词（CONTAINS 和 FREETEXT）以及函数（CONTAINSTABLE 和 FREETEXTTABLE）来查询该列。

单个 varbinary(max) 或 varbinary 列可以存储多种类型的文档。 SQL Server 支持安装了相应筛选器并且在操作系统中可用的任何文档类型。 每个文档的文档类型由该文档的文件扩展名标识。 例如，对于 .doc 文件扩展名，全文搜索将使用支持 Microsoft Word 文档的筛选器。

全文引擎可以利用操作系统中安装的现有筛选器。 在您可以使用操作系统筛选器、断字符和词干分析器之前，您必须将它们加载到服务器实例中，如下所示：

 EXEC sp_fulltext_service @action='load_os_resources', @value=1

--varbinary(max) 或 varbinary 数据

若要对 varbinary(max) 列创建全文索引，全文引擎需要访问 varbinary(max) 列中文档的文件扩展名。 此信息必须存储在一个称为“类型列”的表列中，该列必须与全文索引中的 varbinary(max) 列相关联。 在为文档创建索引时，全文引擎将使用类型列中的文件扩展名来标识要使用的筛选器。

--xml 数据

xml 数据类型列仅存储 XML 文档和片段，并且只有 XML 筛选器用于此类文档。 因此，无需类型列。 在 xml 列上，全文索引会为 XML 元素的内容创建索引，但会忽略 XML 标记。 不为数值的属性值都会进行全文索引。 元素标记用作标记边界。 支持包含多种语言的格式正确的 XML 或 HTML 文档和片段。

6）使用布尔运算符 – AND、OR 和 NOT – 在 CONTAINS 和 CONTAINSTABLE 中

CONTAINS 谓词和 CONTAINSTABLE 函数使用相同的搜索条件。 它们都支持使用布尔运算符（AND、OR、AND NOT）将多个搜索词组合起来，以执行逻辑运算。 例如，可以使用 AND 查找既包含“latte”又包含“New York-style bagel”的行。 例如，可以使用 AND NOT 查找包含“bagel”但不包含“cream cheese”的行。

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

2.使用CONTAINSTABLE谓词 CONTAINSTABLE接受三个参数:

要搜索的表,要搜索的列和搜索的短语.

例:select * from containstable ( Titles,Title,'Cooking AND NOT Computer')

该例返回包含key和rank两列的表

FREETEXTTABLE 语法：

FREETEXTTABLE 是一个函数，用于在 Transact-SQL SELECT 语句的 FROM 字句中对包含基于字符的数据类型的全文索引列执行 SQL Server 全文搜索
语法
FREETEXTTABLE(Table_Name,Column_Name,Language)
Table_Name 表名,需要全文搜索的表名
Column_Name 列名,可以有多个列名中间用逗号隔开
Language 需要搜索的文字
例如:
FREETEXTTABLE([GCP.Product].[ProductPublished],(Name,Keywords),'瑞士军刀')
通过查询   SELECT * FROM  FREETEXTTABLE([GCP.Product].[ProductPublished],(Name,Keywords),'瑞士军刀')