Application of full text retrieval(3)

CONTAINSTABLE syntax
We usually use CONTAINSTABLE in the FROM clause, like this: SELECT * FROM table_name,CONTAINTABLE(fulltext_table,fullText_column,'search condition') WHERE …….

CONTAINSTABLE is almost the same as CONTAINS in terms of query method, so there is no need to go into details. CONTAINSTABLE What is returned is a table that meets the query conditions. We can use it as an ordinary table in SQL statements.

Let’s look at an example to compare the differences between these two tables.
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 wright (.4))' ) AS KEY_TBL
  ON FT_TBL.student_id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK
CONTAINSTABLE The returned table contains two special columns: KEY, RANK.
In the first part, we emphasized that the table that is full-text indexed must have a unique index. This unique index column becomes KEY. We usually use it as a condition for table joins.

When searching on some websites, a number indicating the degree of matching will appear in the results, RANK Similar to this. Its value ranges from 0 to 1000, identifying the degree of matching of each row with the query conditions. The higher the degree, the greater the value of RANK. Normally, the rows are arranged in descending order of RANK.
FREETEXT The syntax
FREETEXT is similar to CONTAINS, but not as precise as CONTAINS. In CONTAINS, there are many requirements for how to write query conditions, and FREETEXT does not exist, it can be any word, phrase or sentence. See the example below:
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )

FREETEXTTABLE syntax
and CONTAINSTABLE Likewise, FREETEXTTABLE returns a table with KEY, RANK. Example:
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]
at Use full text search in asp
Dim cnn
Dim rs
Dim 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;datasource=.;initial catalog=books;user id=sa;pasWord=;"
rs.Open strSQL,cnn


The above example is very simple and is only for illustration. As long as you master the syntax of CONTAINS and CONTAINSTABLE, the usage is the same as that of general Same as ADO queries.

The above is the content of full text retrieval application (3). For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!