关于Full Text的一些Sql

USE AESOP;

CREATE FULLTEXT CATALOG AesopFT;

CREATE FULLTEXT INDEX ON dbo.Fable(Title, Moral, Fabletext)

KEY INDEX FablePK ON AesopFT

WITH CHANGE_TRACKING AUTO;

ALTER FULLTEXT INDEX ON Fable START FULL POPULATION;

ALTER FULLTEXT INDEX ON Fable START Incremental POPULATION

Remove a full-text catalog:

DROP FULLTEXT INDEX ON dbo.Fable

DROP FULLTEXT CATALOG AesopFT

USE Aesop;

SELECT Title

FROM Fable

WHERE CONTAINS (Fable.*,‘Lion’);

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;

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;

SELECT Title

FROM Fable

WHERE CONTAINS (FableText,‘Tortoise AND Hare’);

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘Thrifty’)

AND CONTAINS(*,‘supperless’)

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘ "Hunt*" ’);

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘ "Wolf! Wolf!" ’);

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘pardoned NEAR forest’);

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘lion NEAR paw NEAR bleeding’);

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;

SELECT Title

FROM Fable

WHERE CONTAINS (*,‘FORMSOF(INFLECTIONAL,fly)’);

SELECT * FROM TableName WHERE CONTAINS(*,‘FORMSOF(Thesaurus,"IE")’);

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;

SELECT Title

FROM Fable

WHERE FREETEXT

(*,‘The tortoise beat the hare in the big race’);

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;