关于Full Text的一些Sql

/*创建全文索引，创建后可用alter fulltext index修改*/
USE AESOP;
CREATE FULLTEXT CATALOG AesopFT;
CREATE FULLTEXT INDEX ON dbo.Fable(Title, Moral, Fabletext)
KEY INDEX FablePK ON AesopFT
WITH CHANGE_TRACKING AUTO;


/*执行Full population*/
ALTER FULLTEXT INDEX ON Fable START FULL POPULATION;

/*Incremental population:*/
ALTER FULLTEXT INDEX ON Fable START Incremental POPULATION

Remove a full-text catalog:
DROP FULLTEXT INDEX ON dbo.Fable
DROP FULLTEXT CATALOG AesopFT


/*contains函数,第一个参数为列名(如果from有多个表，还要加上表名，如果是所有列则使用*)*/
USE Aesop;
SELECT Title
FROM Fable
WHERE CONTAINS (Fable.*,‘Lion’);


/*containstable*/
SELECT *
FROM CONTAINSTABLE (Fable, *, ‘Lion’);

SELECT Fable.Title, FTS.Rank
FROM Fable
INNER JOIN CONTAINSTABLE (Fable, *, ‘Lion’) AS FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC;

/*containstable还能添加第四个参数，取top n行，类似sql的top n*/
SELECT Fable.Title, FTS.Rank
FROM Fable
INNER JOIN CONTAINSTABLE (Fable, *, ‘Lion’, 2) AS FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC;

/*Multiple-word searches:Multiple words may be included in the search by means of the OR and AND conjunctions.
contains可以在多列中搜一个词，但是只能在一个列中搜多个词
*/
SELECT Title
FROM Fable
WHERE CONTAINS (FableText,‘Tortoise AND Hare’);


/*可以实现在多列中查多个词，但是这个有性能问题*/
SELECT Title
FROM Fable
WHERE CONTAINS (*,‘Thrifty’)
AND CONTAINS(*,‘supperless’)


/*Searches with wildcards:使用双引号和星号，另外星号只能在词的结尾，不能在开头。*/
SELECT Title
FROM Fable
WHERE CONTAINS (*,‘ "Hunt*" ’);



/*Phrase searches:要搜索full phrases，就用双引号把那个phrase给括起来*/
SELECT Title
FROM Fable
WHERE CONTAINS (*,‘ "Wolf! Wolf!" ’);


/*Word-proximity searches:使用near选项，The relative distance between the words is calculated, and, if the words are close enough (within about 30 words, depending on the size of the text), then full-text search returns a true for the row.*/
SELECT Title
FROM Fable
WHERE CONTAINS (*,‘pardoned NEAR forest’);

SELECT Title
FROM Fable
WHERE CONTAINS (*,‘lion NEAR paw NEAR bleeding’);

/*The proximity feature can be used with CONTAINSTABLE; the RANK indicates relative proximity.*/
SELECT Fable.Title, FTS.Rank
FROM Fable
INNER JOIN CONTAINSTABLE (Fable, *,‘life NEAR death’) AS FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC;

/*Word-inflection searches:首先说明一下这个是对英文那样的语言有效吧应该，比如fly这个动词，它会搜出它的过去式flew，现在进行式flying等等，但是它不能以名词为词根搜索出其相对的动词，相反亦然*/
SELECT Title
FROM Fable
WHERE CONTAINS (*,‘FORMSOF(INFLECTIONAL,fly)’);


/*Thesaurus searches:这种查询可以查询同义词或者进行词替换，这种查询要依靠thesaurus file（具体位置要依语言和和服务器而定），thesaurus file文件名格式为：TSXXX.xml其中XXX语言代码，比如：ENU for U.S. English, ENG for U.K. English。
其一般有如下两种形式
<expansion>
_{Internet Explorer}
_IE
_IE5
</expansion>
This will convert any searches on ‘‘IE’’ to search on ‘‘IE’’ or ‘‘IE5’’ or ‘‘Internet Explorer.’’

<replacement>
<pat>sex</pat>
_gender
</replacement>
这种的模式是当你搜寻sex的时候，会用gender来替代sex
A FREETEXT query will automatically use the thesaurus file for the language type.
*/
SELECT * FROM TableName WHERE CONTAINS(*,‘FORMSOF(Thesaurus,"IE")’);


/*Variable-word-weight searches:In a search for multiple words, relative weight may be assigned, making one word critical to the search and another word much less important. The weights are set on a scale of 0.0 to 1.0.

The ISABOUT option enables weighting, and any hit on the given word allows the rows to be returned,
so it functions as an implied Boolean OR operator.

可以搜索词或短语，并可以指定加权值。权值用介于 0.0 到 1.0 之间的一个数字来表示，用于指示一组词和短语中的每个词和短语的重要程度。权值的最低值是 0.0，最高值是 1.0。SQL Server 2008 对那些包含较多指定词的行赋予较高的排名。
*/
SELECT Fable.Title, FTS.Rank
FROM Fable
INNER JOIN CONTAINSTABLE
(Fable, FableText,
‘ISABOUT (Lion weight (.5),
Brave weight (.5),
Eagle weight (.5))’) AS FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC;


/*Fuzzy Searches:使用freetext来进行搜索。*/
SELECT Title
FROM Fable
WHERE FREETEXT
(*,‘The tortoise beat the hare in the big race’);

/*和containstable一样返回一个评级的排名*/
SELECT Fable.Title, FTS.Rank
FROM Fable
INNER JOIN FREETEXTTABLE
(Fable, *, ‘The brave hunter kills the lion’) AS FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC;