SQL Full-Text Search example

Examples of use of contains in SQL, detailed parameters

Full-text index--contains syntax we usually use CONTAINS in the WHERE clause, just like this: SELECT * FROM table_name WHERE CONTAINS (fulltext_column, ' search content S '). We learn by example, assuming that there is a table students, where address is a column of full-text retrieval.

1. Check the student's address in Beijing SELECT student_id,student_name from students where CONTAINS (address, ' Beijing ')

Remark:beijing is a word that is enclosed in single quotation marks.

2. Check the student's address in Hebei province SELECT student_id,student_name from students WHERE CONTAINS (address, ' "Heibei province")

Remark:hebei Province is a phrase that is enclosed in double quotes in single quotes.

3. Check the student's address in Hebei province or Beijing students SELECT student_id,student_name from students WHERE CONTAINS (address, ' Heibei province ' or Beijing ')

Remark: You can specify logical operators (including and, and Not,or).

4. Query the address of the word "Nanjing Road" SELECT student_id,student_name from students WHERE CONTAINS (address, ' Nanjing near Road ')

Remark: The above query will return addresses containing the words ' Nanjing Road ', ' Nanjing East Road ', ' Nanjing West Road '. A near B indicates the condition: A is close to B.

5. Query the address starting with ' lake ' SELECT student_id,student_name from students WHERE CONTAINS (address, ' "hu*" ')

Remark: The above query will return addresses containing the words ' Hubei ', ' Hunan ', and so on. Remember is *, not%.

6. Similar weighted query SELECT student_id,student_name from students WHERE CONTAINS (address, ' ISABOUT (city weight (. 8), County Wright (. 4)) ' )

Remark:isabout is the keyword for this query, weight specifies a number between 0~1 and similar coefficients (my understanding). Indicates that different conditions have different emphasis.

7. Multi-state query for words SELECT student_id,student_name from students WHERE CONTAINS (address, ' formsof (inflectional,street) ')

Remark: The query will return addresses that contain the words ' street ', ' Streets ', and so on. For the verb will return its different tenses, such as: dry, will return dry,dried,drying and so on.

Inflectional in English means "derivative words".

And the parameters used in conjunction with FORMSOF are thesaurus, which are synonyms.

This query relies on thesaurus file (depending on the language and server), thesaurus file file name format is: tsxxx.xml where xxx language code, such as:

CHS for Simplified Chinese

ENU for U.S. 中文版

ENG for U.K. 中文版. It generally has the following two kinds of forms
Internet Explorer IE IE5

Example: SELECT student_id,student_name from students WHERE CONTAINS (address, ' formsof (thesaurus,ie) ')

Search IE will return to Internet explorer,ie5