Collection of full-text retrieval of SQL Server 1

Use of full-text search in SQL Server 2000 (1)

Introduction

Microsoft's SQL Server database is a relational database system that occupies a wide market in low-end and mid-end enterprise applications, with its simplicity, convenience, and ease of use, it is favored by many software developers and database administrators. However, since the database system before SQL Server 7.0 does not have the full-text retrieval function, it is a small pity that it cannot provide services such as text content search. From SQL Server 7.0 to today's SQL Server 2000, full-text retrieval is available, this allows you to efficiently retrieve text data stored in database char, varchar, text, ntext, nchar, nvarchar, and other data type columns.

Create full-text index

Before full-text retrieval, you must create and fill in the full-text index of the database. To support full-text indexing, SQL Server 7.0 adds stored procedures and transact-SQL statements. The procedure for creating a full-text index using these stored procedures is as follows (the name of the called stored procedure is in parentheses ):

1. Start the full-text processing function of the database (sp_fulltext _

Database );;

2. Create a full-text search directory (sp_fulltext_catalog );

3. register the table (sp_fulltext_table) that requires full-text indexing in the full-text search directory );

4. Name of the column (sp_fulltext _

Column );;

5. Create a full-text index for the table (sp_fulltext_table );;

6. Fill in the full-text search directory (sp_fulltext_catalog ).

The following example shows how to create a full-text index. In this example, create a full-text index for the title column and the Notes column in the test database book table.

Use test // open the database

// Enable full-text index support and enable full-text search for SQL Server

Execute sp_fulltext_database 'enable'

// Create the full-text search directory ft_test

Execute sp_fulltext_catalog 'ft _ test', 'create'

Create full-text index data element for the title column. pk_title is the unique index created by the primary key in the book table. This parameter is required.

Execute sp_fulltext_table 'book', 'create', 'ft _ test', 'pk _ title'

// Set the full-text index column name

Execute sp_fulltext_column 'book', 'title', 'add'

Execute sp_fulltext_column 'book', 'note', 'add'

// Create a full-text index

Execute sp_fulltext_table 'book', 'activate'

// Fill in the full-text INDEX DIRECTORY

Execute sp_fulltext_catalog 'ft _ test', 'start _ full'

So far, the full-text index has been created.

Full-text search

SQL Server 2000 provides the following full-text search statements: contains and freetext. The contains statement is used to search for a word or phrase in all or specified columns of a table. The prefix of a word or phrase is similar to that of a word; A derived word of a word; a repeated word.

The syntax format of the contains statement is:

Contains ({Column | *}), <contains_search

_ Condition>)

Column is a search column. When "*" is used, it indicates that all full-text index columns in the table are searched. Contains_search _

Condition describes the search content of the contains statement. The syntax format is:

{<Simple_term> | <prefix_term> | <proximity_term> | <generation_term> | <weighted_term >}[ {and | and not | or} <contains_search_condition>}] [... n]

The simple_term and prefix_term parameters are briefly described below:

Simple_term is the word or phrase searched by the contains statement. When searching for a phrase, double quotation marks must be used as the delimiter. The format is:

{'Word' | "phrase "}

Prefix_term indicates the prefix of the word or phrase searched by the contains statement. The format is:

{"Word *" | "phrase *"}

For example, the following statement retrieves the name of a book containing the "Database" or "computer" string and Its annotation information in the title column and Notes column of the book table:

Select title, notes

From book

Where contains (tilte, 'database') or contains (notes, 'database ')

Or contains (title, 'computer ') or contains (notes, 'computer ')

The freetext statement is used to search for a free text string in all or specified columns of a table and return data rows matching the string. Therefore, the freetext statement is also called a free full-text query.

The syntax format of the freetext statement is: freetext ({Column | *}, 'freetext _ string ')

Column is the column to be searched. If "*" is used, all full-text index columns in the table are searched. The freetext_string parameter indicates the searched free text format string.

For example, the following statement uses freetext to search data rows in the book table that contain the "successful life" string:

Select title, notes

From book

Where freetext (*, 'successful life ')