Full-Text Search

Concepts: word breakers, stemmers, stoplists, thesaurus files, iFilters, semantic search (SEMANTICKEYPHRASETABLE).

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Products(ProductName LANGUAGE 1033, Description)
    KEY INDEX PK_Products ON ftCatalog WITH CHANGE_TRACKING AUTO;

SELECT * FROM dbo.Products WHERE CONTAINS(Description, '"wireless" AND "mouse"');
SELECT * FROM dbo.Products WHERE CONTAINS(Description, 'FORMSOF(INFLECTIONAL, run)');
SELECT * FROM dbo.Products WHERE CONTAINS(Description, 'NEAR((battery, life), 5)');
SELECT * FROM dbo.Products WHERE CONTAINS(Description, '"blue*"');           -- prefix
SELECT * FROM dbo.Products WHERE FREETEXT(Description, 'cheap fast laptop');

SELECT p.ProductName, k.RANK
FROM CONTAINSTABLE(dbo.Products, Description, 'ISABOUT (laptop WEIGHT(.8), gaming WEIGHT(.4))') k
JOIN dbo.Products p ON p.ProductID = k.[KEY] ORDER BY k.RANK DESC;