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 (m.sbmmt.com)!