|
|
Posted onYesterday at 20:20
|
|
|
|
Full-text search is an optional component of the SQL Server database engine. If you did not select full-text search when installing SQL Server, run the SQL Server installer again to add it.
Overview
A full-text index includes one or more character-based columns in a table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max), and FILESTREAM. Each full-text index creates an index for one or more columns in the table, and each column can use a specific language.
Full-text queries operate on words and phrases according to the rules of specific languages (such as English or Japanese), thereby performing language searches based on the text data in the full-text index. Full-text queries can include simple words and phrases, or multiple forms of words or phrases. A full-text query returns all documents containing at least one match (also known as a "hit"). A match occurs when the target document contains all the terms specified in the full-text query and matches any other search criteria (such as the distance between matching terms).
Linux Docker MSSQL 2022 Full-Text Search Service Installation
References are as follows:
Check if SQL SERVER's full-text search feature is installed
The command is as follows:
SQL Server Full-Text Search, Full-Text Index
First, create a new test library and insert test data. The script is as follows:
Create a full-text table of contents
A full-text table of contents is used to store the full-text index. You can create it directly using the following scripts:
Create full-text indexes for database tables
When creating a full-text index for a database table, you need to specify the corresponding language. Because word segmentation varies across languages, SQL Server will use the corresponding language's word segmentation to process the data in the table. SQL Server's full-text search supports about 50 different languages, allowing you to view all supported languages by querying sys.fulltext_languages tables. Command:
As shown below:
Next, we add a full-text index for Chinese Simplified to the Title and Content fields in the News table:
Full-text search for predicates and functions
Full-text queries use full-text predicates (CONTAINSandFREETEXT) and full-text functions (CONTAINSTABLEandFREETEXTTABLE）。 They support the complex Transact-SQL syntax, which supports various forms of query terms.
The difference between CONTAINS and FREETEXT
CONTAINS: The hyperlink login is visible.
Use precise matching methods to find specific words or phrases in the text.
Supports complex query conditions, such as proximity search (for example, words must appear near each other), prefix search, and more.
It is usually used in situations where more specific control over search results is needed.
FREETEXT: The hyperlink login is visible.
Provides a more flexible and natural language-based search experience.
No special search criteria are required; It automatically parses the input text and tries to find related content.
It is more suitable for users who want to perform simple keyword searches like Google, without worrying about specific locations or formats.
The commands are as follows:
As shown below:
CONTAINSTABLE and FREETEXTTABLE
CONTAINSTABLE returns tables that meet the query criteria. In SQL statements, we can treat them as regular tables, and queries using CONTAINSTABLE return a RANK value and a KEY for each row. RANK is used to indicate the degree of correlation match, with values between 0~1000, and KEY is the ID of the main table. FREETEXTTABLE first segments the words and sentences to be queried, then queries for matching. The command is as follows:
As shown below:
Additionally, you can check the segmentation results by querying sys.dm_fts_parser:
As shown below:
issue
Question: CONTAINS does not support fuzzy queries by default. If you enter CONTAINS(Column, 'apple'), it will match the root word (e.g., apples) but not pineapple.
Solution: If fuzzy queries are needed, they must be placed before and after wildcardsAdd double quotation marks and an asterisk：CONTAINS(Column, '"*apple*"')
Reference:
The hyperlink login is visible.
The hyperlink login is visible. |
Previous:Remote work, work, and freelance work platform website
|
Landlord|
Posted on58 seconds ago
|
