SQL Server 1: full-text search (1)

This chapter introduces the full-text index under SQL Server 2008 R2. It can index character-type columns (such as varchar and text) in data and query full-text search through indexes. First, we will briefly introduce the differences between regular indexes and full-text indexes. For example, OK. Next we will use the stored procedure provided by SQL Server to create a full-text index. The specific steps are as follows: (1) start the full-text processing function of the database (sp_fulltext_datebase );
(2) create a full-text directory (sp_fulltext_catalog );
(3) register the table (sp_fulltext_table) that requires full-text indexing in the full-text directory );
(4) Name of the full-text search column in the table (sp_fulltext_column)
(5) create a full-text index for the table (sp_fulltext_table );
(6) Fill in the full-text index (sp_fulltext_catalog ).

Next, we will use an example to demonstrate it step by step:

 

SQL Server full-text search

Use dbfulltext

-- Create a table
Createtable student
(
Id intprimarykeyidentity (1, 1) notnull,
Name nvarchar (30) null,
Familyaddresses nvarchar (100) null,
Schooladdress nvarchar (100) null
)

-- Insert some data
Insertinto student values ('savea ', '2017 Suffolk ln.', '2017 oak st .')
Insertinto student values ('victe', '2, Rue du Commerce ', '23 tsawassen Blvd .')
Insertinto student values ('blonp ', '24, place KL éber', '25, Rue Lauriston ')
Insertinto student values ('Paris ', '2017, Boulevard charonne', '2017 Baker Blvd .')
Insertinto student values ('oldwo', '2017 Bering st. ', '2017 Milton dr .')
Insertinto student values ('wandk', 'adenauerallee 100', 'invalid kergatan 24 ')
Insertinto student values ('bergs ', 'berguvsv ägen 8', 'carrera 22 con ave. Carlos soublette #8-35 ')
Insertinto student values ('santg ', 'carrera 52 con ave. bolí var #65-98 Llano Largo', 'erling skakkes gate 78 ')
Insertinto student values ('oceanc', 'grenzacherweg 100', 'jardim das Rosas N. 32 ')
Insertinto student values ('leshs', 'sierras de Granada 100', 'via Ludovico Il Moro 22 ')
Insertinto student values ('simob', 'South house 300 Queensbridge ', 'p.o. Box 100 ')

-- Check whether dbfulltext supports full-text indexing. If full-text indexing is not supported, use sp_fulltext_datebase to enable this function.
If (selectdatabaseproperty ('dbfulltext', 'isfulltextenables ') = 0
Exec sp_fulltext_database 'enable'

-- Create a full-text directory ('full-text directory name', 'create/delete ')
Exec sp_fulltext_catalog 'ft _ student ', 'create'

-- Create a full-text index ('table name', 'create/delete', 'name', and 'constraint name'). The constraint name here is the primary key constraint automatically generated during table creation.
Exec sp_fulltext_table 'student ', 'create', 'ft _ student', 'pk _ student'

-- Set full-text index columns ('table name', 'column name', 'add/delete ')
Exec sp_fulltext_column 'student ', 'familyaddress', 'add'
Exec sp_fulltext_column 'student ', 'schooladdress', 'add'

-- Activate the full-text retrieval capability of a table, that is, register the table in the full-text directory.
Exec sp_fulltext_table 'student ', 'activate'

-- Fill in the full-text INDEX DIRECTORY
Exec sp_fulltext_catalog 'ft _ student ', 'start _ full'

-- Test
Select * from student wherecontains (familyaddress, 'south ')
Select * from student wherecontains (schooladdress, 'Dr. ') OK. Now the full-text search of SQL server code has been completed. In fact, in SQL Server 2008 R2, there is no need for the above Code to operate on the stored procedure to create a full-text index. It comes with 'full text catalogs ', we can create a full-text index manually (the implementation process is to call the stored procedure, but it is omitted here). First, find the directory storage-> full text catalogs, create a new full text catalog, such as opening it, and select the columns to be indexed in full text. After saving the column, you can perform full text search as shown in the preceding figure.