SQL Server 全文檢索進階程式設計：CONTAINS、FREETEXT 與權重查詢實戰

前言

安裝設定與基礎查詢請參考前兩篇。本文深入探討 CONTAINS、FREETEXT 及其資料表值函數變體的進階語法與實戰技巧。

CONTAINS 進階語法

布林邏輯運算子

-- AND: 同時包含
SELECT username FROM member WHERE CONTAINS(userinfo, '"作家" AND "木匠"');

-- OR: 包含任一
SELECT username FROM member WHERE CONTAINS(userinfo, '"作家" OR "木匠"');

-- AND NOT: 包含前者但不包含後者
SELECT username FROM member WHERE CONTAINS(userinfo, '"作家" AND NOT "木匠"');

NEAR: 接近度搜尋

找出兩詞距離較近的文件 (同段落/鄰近句子)。

SELECT Content FROM MicrosoftRecord 
WHERE CONTAINS(Content, '"比爾·蓋茨" NEAR "保羅·艾倫"');

FORMSOF INFLECTIONAL: 詞形變化

自動比對動詞時態、名詞單複數等 (英文有效，中文支援有限)。

SELECT ProductName FROM Products 
WHERE CONTAINS(ProductName, 'FORMSOF (INFLECTIONAL, dry)');
-- 比對 dry, dried, drying, dries 等

前置詞搜尋 (*)

SELECT ProductName FROM Products 
WHERE CONTAINS(ProductName, '"dis*"');
-- 比對 disease, display, distance 等

ISABOUT WEIGHT: 加權排序

權重 0.0~1.0，影響回傳順序。

SELECT CategoryName, Description FROM Categories 
WHERE CONTAINS(Description, 
    'ISABOUT (spread WEIGHT (.8), sauces WEIGHT (.4), relishes WEIGHT (.2))');

CONTAINSTABLE: 回傳資料表供進一步查詢

SELECT FT_TBL.Title, KEY_TBL.RANK
FROM Docs AS FT_TBL
INNER JOIN CONTAINSTABLE(Docs, Content, '"SQL Server"') AS KEY_TBL
    ON FT_TBL.DocID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

回傳欄位：[KEY] (唯一鍵值)、RANK (相關性分數 0-1000)。

FREETEXT: 自然語言模糊查詢

自動斷詞、過濾停用詞、比對詞形變化 (類似 FORMSOF)，中文支援有限。

SELECT CategoryName FROM Categories 
WHERE FREETEXT(Description, 'sweetest candy bread and dry meat');

FREETEXTTABLE: 回傳資料表版本

SELECT FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL
INNER JOIN FREETEXTTABLE(Categories, Description, 'sweet candy bread') AS KEY_TBL
    ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

實務建議

• 精確語意查詢用 CONTAINS，自然語言輸入用 FREETEXT
• 需排序相關性時，改用 CONTAINSTABLE/FREETEXTTABLE 配合 RANK
• 中文環境建議搭配斷詞器 (如 Microsoft 簡體/繁體中文斷詞器) 並測試效果
• 全文索引定期重建/更新排程，避免碎片化影響查詢效能