Full-Text Search titbits

To create a full-text search by way of command

The SQL2005 system database provides us with a complete set of stored procedures related to full-text retrieval, and learning to call with less effort.

Enable and disable full-text indexing

1, the database enable Full-text search

Exec sp_fulltext_database ' Enable '

2. Database Shutdown Full-text Search

Exec sp_fulltext_database ' Disable '

Ii. Establishment of Full-text catalogs

1. Create a Full-text Catalog

Exec sp_fulltext_catalog ' directory name ', ' Create '
2. Rebuilding Full-text Catalogs

Exec sp_fulltext_catalog ' directory name ', ' Rebulid '
3. Start full-text catalog fill

Exec sp_fulltext_catalog ' directory name ', ' start_full '
4, stop the full text catalog fill

Exec sp_fulltext_catalog ' directory name ', ' Stop '
5. Delete Full-text Catalogs

SELECT * from Sys.fulltext_catalogs (find directory name)
Exec sp_fulltext_catalog ' directory name ', ' drop '

Third, establish Full-text index data for table

1. Unique index established

Exec sp_fulltext_table ' table name ', ' Create ', ' Directory name ', ' Unique index health '
2, set the full-text index column name

Exec sp_fulltext_column ' table name ', column name ', ' Add '
Go
3. Activate Full-text indexing

Exec sp_fulltext_table ' table name ', ' Activate
4. Populate Full-text Index Catalog

Exec sp_fulltext_catalog ' table name ', ' start_full '

IV. using Transact-SQL for Full-text search, we use predicates such as contains, FREETEXT, etc.

1, the search ProductName, that contains B and contains C of all the columns
SELECT *
From table name WHERE CONTAINS (column name, ' c* "and" b* "') (* indicates matching keyword starting with the * number before character)
2, the search ProductName, that contains a phrase all the columns
SELECT *
From table name WHERE FREETEXT (column name, ' phrase ')

V. Full-text query-related system stored procedures

(1) to start the Full-text processing function of the database (sp_fulltext_datebase);
(2) Establishment of Full-text catalogs (sp_fulltext_catalog);
(3) Register the table (sp_fulltext_table) that requires Full-text indexing in the Full-text catalog;
(4) Indicate the name of the column in the table that requires Full-text search (sp_fulltext_column)
(5) Create a Full-text index (sp_fulltext_table) for the table;
(6) Populate Full-text indexing (sp_fulltext_catalog).

Create a Full-text index in Enterprise Manager

SQL2005 Enterprise Edition features

You can create a Full-text index from a graphical interface. (SQL2005 Express can directly enable Full-text Indexing Service to load the Enterprise Edition to create a good Full-text index.) ）

Note: A manually created Full-text search needs to be updated regularly. Full-Text search automatically created by SQL has a certain latency after automatic update is enabled.