用於 FROM 子句,回傳包含 KEY (唯一索引鍵值) 與 RANK (0-1000 相關性分數) 的資料表值函數,可直接 JOIN 基底表。
SELECT FT_TBL.student_name, FT_TBL.student_score, KEY_TBL.RANK
FROM report AS FT_TBL
INNER JOIN CONTAINSTABLE(student, address, 'ISABOUT (city WEIGHT(.8), county WEIGHT(.4))') AS KEY_TBL
ON FT_TBL.student_id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
KEY:對應全文索引定義時指定的唯一索引鍵欄位。RANK:相關性分數,值越大越符合查詢條件,通常依此降序排序。無需精確語法,直接輸入單字、片語或句子,全文引擎自動斷詞、詞幹分析、同義詞展開。
SELECT CategoryName
FROM Categories
WHERE FREETEXT(Description, 'sweetest candy bread and dry meat')
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL
INNER JOIN FREETEXTTABLE(Categories, Description, 'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
<%
Dim cnn, rs, strSQL
strSQL = "SELECT book_name FROM books WHERE CONTAINS(description, '" & Request("search_condition") & "')"
Set cnn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cnn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=books;User ID=sa;Password=;"
rs.Open strSQL, cnn
' 輸出結果
Do While Not rs.EOF
Response.Write rs("book_name") & "<br>"
rs.MoveNext
Loop
rs.Close: cnn.Close
Set rs = Nothing: Set cnn = Nothing
%>
Request("search_condition") 存在 **SQL 注入** 風險,實務應使用參數化命令 (Command.Parameters) 或預存程序。On Error Resume Next / Try Catch 處理。CONTAINS 精確語法:單字、片語、邏輯運算子、NEAR、前綴 *、ISABOUT 加權、FORMSOF 詞形變化。CONTAINSTABLE/FREETEXT/FREETEXTTABLE 進階查詢與 ASP 整合。