SQL Server Full-text Search (full-text) syntax _mssql

SQL Server Full-text Search has two ways of searching, one is contains, the other is FREETEXT. The former is contained, similar to

Like '% keyword% ', the latter is a paragraph of words after the search for each word.

Specific syntax:
contains:

SELECT Field 1, Field 2
From table name
WHERE contains (field, ' "Word one" or "word Two" ")

Sort by similarity of search results
SELECT Field 1, Field 2
From table name
INNER JOIN CONTAINSTABLE (table name, field, ' "Word one" or "word two" ', ten) as K
on table name. ID = K.[key]
ORDER BY K.rank DESC


FREETEXT:

SELECT Field 1, Field 2
From table name
WHERE freetext (field, ' word two ')

Sort by similarity of search results
SELECT Field 1, Field 2
From table name
INNER JOIN FREETEXTTABLE (table name, field, ' Word two ', ten) as K
on table name. ID = K.[key]
ORDER BY K.rank DESC

10 of the freetexttable or containstable in the preceding paragraph takes 10 data

Recently searched the full text search, found some problems, now summarized as follows:

Full-text indexing and query Concepts (excerpt from SQL online Help)

The primary design requirement for full-text indexing, querying, and synchronization is that there is a unique Full-text key column (or a single-column primary key) on all the tables that are registered for Full-text retrieval. The Full-text index tracks the important words used and where they are located.

For example, suppose you have a full-text index on a devtools table. Full-text indexing may indicate that the word Microsoft is found in the No. 423 and No. 982 words of the Abstract column, and that the row is associated with ProductID 6. The index structure supports efficient retrieval of all items that contain indexed words, as well as advanced retrieval operations such as phrase retrieval and proximity retrieval.

To prevent full-text indexing from becoming bloated by including many words that are not helpful for retrieval, extra words such as a, and, is, or the are ignored. For example, the designation "the products ordered during these summer months" is the same as the specified "products ordered during summer months". The rows with both strings are returned.

A list of noise words in multiple languages is provided under the directory \mssql\ftdata\sqlserver\config. This directory is created when you install the Microsoft®sql Server™ with Full-text retrieval support, and the noise Word file is installed. The noise word file can be edited. For example, a high tech company's system administrator can add word computer to their jamming vocabulary. (If you edit the noise word file, you must repopulate the Full-text catalog before the change takes effect.) The following table shows the noise word file and its corresponding language.

Noise Word File language
-------------- ---------
Noise.chs Simplified Chinese
Noise.cht Traditional Chinese
Noise.dat language Neutral
Noise.deu German
Noise.eng English (UK)
Noise.enu English (USA)
NOISE.ESN Spanish
Noise.fra French
Noise.ita Italian language
NOISE.JPN Japanese
Noise.kor Han Wen
NOISE.NLD Dutch language
Noise.sve Swedish language


When processing a full-text query, the retrieval engine returns the key value of the row that satisfies the check criteria to Microsoft SQL Server. For example, there is a scifi table, where the Book_no column is the primary key column.

Book_no Writer Title
-------- ----------- --------------------------
A025 Asimov Foundation ' s Edge
A027 Asimov Foundation and Empire
C011 Clarke Childhood ' s end
V109 Verne Mysterious Island


Suppose you want to use a Full-text search query to find the title that contains the word Foundation. In this case, the values A025 and A027 are obtained from the Full-text index. SQL Server then uses these key values and information from other columns to be queried.

The following table shows the languages that are used to store full-text indexing data. These languages are based on the Unicode collation locale identifier selected during SQL Server setup.

Unicode collation locale identifier language used by the Full-text data store
------------------------------- -----------------------
Chinese phonetic symbols (Taiwan) Traditional Chinese
Simplified Chinese Pinyin
Chinese Strokes Simplified Chinese
Chinese strokes (Taiwan) Traditional Chinese
Dutch Dutch language
English (UK) English (UK)
French French
Universal Unicode English (United States)
German German
Devon Phone Book German
Italian Italian language
Japanese Japanese
Japanese Unicode Japanese
Han Wenhanwen
Korean Unicode Korean
Spanish (modern) Spanish
Swedish/Finnish Swedish



All other Unicode collation locale identifier values that are not in this list map to the hyphenation and stemming delimiters of neutral language words that use spaces to separate words.

Explains that the Unicode collation locale identifier setting is used for all data types that can be full-text indexed (such as char, nchar, and so on). If the language type set for the sort order of a char, varchar, or text type column is not a Unicode collation locale identifier language, the Unicode collation is still used when full-text indexing and querying for columns of char, varchar, and text types The locale identifier value.



Create a Full-text index (for example, in index image, other type fields are roughly the same)

Title Full-text index image column, all Raiders!
Author Pengdali [Original]
Keyword Full-text index image


Today, a "once-in-a-century" blackout, read the book for a day. The evening made a full text index, decided to paste out the experience, I try to write in detail, we learn together, welcome to correct me!

1. Start Microsoft Search Service
Start Menu-->sql program Group--> Service Manager--> dropdown basket-->microsoft Search service--> start it

2,
.. \microsoft SQL Server\mssql\ftdata\sqlserver\config\ Directory to build a non-empty noise.chs file
Non-empty Noise.chs file, some people say is empty noise.chs file, but I write a few useless letters every time.

3, the establishment of the environment
Open Query Analyzer--> execute the following script:
--------------------------------------------
CREATE DATABASE test---creating test databases
Use test---Select the test database
Create TABLE Dali (ID int not null primary key,myimage image,filetype varchar (255), Filenmae varchar (255))---creating a Dali table
The three columns in the--dali table must be id,myimage,filetype, because to index the image column, you must have a primary key column, an image column, a column that holds the file type
--we know that file types are differentiated by extension in Windows systems, so the filetype column is the extension used to place the file.
--------------------------------------------

Sp_fulltext_database ' Enable '-Enable database for Full-text indexing
Sp_fulltext_catalog ' My_fulldir ', ' create '---creating a full-text catalog called My_fulldif

declare @Key sysname; Select @Key =c.name from syscolumns a,sysconstraints b,sysobjects C where a.id=object_id (' Dali ') and a.name= ' id ' and a.id=b . ID and B.constid=c.id and c.name like ' pk% '
EXEC sp_fulltext_table ' Dali ', ' Create ', ' my_fulldir ', @Key----These two sentences are for full-text indexing, marking tables

Sp_fulltext_column ' Dali ', ' myimage ', ' Add ', 0x0804, ' FileType '---this is the specified myimage column for full-text indexed, FileType is a type column
------------------------------------------------
4. A Word file with a name extension of doc, an Excel file with an extension of XLS, a Web page file with an extension of HTM, and a picture with a BMP extension in C disk.
A total of 4, you can be placed according to the actual situation!

5. Inserting data
Create the following stored procedure
--------------------------------------------------
CREATE PROCEDURE sp_textcopy
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char (1)
As
/* This is the use of the Textcopy tool to insert files into the database, if there is a foreground tool can use the foreground development tools to insert the file, here for the demo * *
DECLARE @exec_str varchar (255)
SELECT @exec_str = ' textcopy/s ' + @srvname + '/u ' + @login + '/p ' + @password + '/d ' + @dbname + '/T ' + @tbname + '/C ' + @colname + '/w ' ' + @whereclause + '/F ' + @filename + ' '/' + @direction
EXEC Master.. xp_cmdshell @exec_str
----------------------------------------------------

Insert Dali values (1,0x, ' Doc ', ' vigorously Doc ')---where the second column is 0x it is a 16 binary number corresponding to the image column, is required, do not write null, the third column is a file type, both extension

Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ vigorously doc.doc ', ' where id=1 ', ' I '
-------The parameters are: instance name, username, password, database name, table name, image column name, path and filename, condition (you must make sure that it only selects one line), I
--------------------------------------------------------------------------------------------------------------- ------
Insert Dali values (2,0x, ' bmp ', ' picture ')
Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ picture. bmp ', ' where id=2 ', ' I '--note that the condition is id=2

Insert Dali values (3,0x, ' xls ', ' Excel file ')
Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\Excel file. xls ', ' where id=3 ', ' I '--note that the condition is id=3

Insert Dali values (4,0x, ' htm ', ' web ')
Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Test ', ' Dali ', ' myimage ', ' c:\ Web page. htm ', ' where id=4 ', ' I '--note that the condition is id=4

----------The above statement, to ensure the same type, the path is correct, the only correct condition should be

6. Populate Full-text Indexing

sp_fulltext_table ' Dali ', ' start_full '---the first argument is the table name, and the second parameter is the full population of the Full-text index of the startup table

7. You can start your experiment.

SELECT * from Dali where contains (MyImage, ' J teacher ')

SELECT * from Dali where contains (MyImage, ' Sea teacher ')

------End----------
--Debugging Environment: SQLSERVER2000 Enterprise Edition, Windows2000 Advanced Server

Several issues in Full-text indexing:

1. Error during search:
Server: Message 7619, Level 16, State 1, line 2
The query clause contains only the words that are ignored

This situation modifies the noise word list file for the corresponding language under \mssql\ftdata\sqlserver\config

2. The interference Word file has been modified, and the above problems still occur when querying Chinese
A. Check your SQL first to see if the latest patches are installed, and the method to check is to run in Query Analyzer:
SELECT @ @version
If the version number shown below is 8.00.760, you are not installing the SP3 patch to install it.

SQL Patch Downloads:
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID= 9032f608-160a-4537-a2b6-4cb265b80766

Note that after the download, the implementation of the time is decompression, to be extracted in the directory after the implementation of Setup.bat is the real installation

B. When configuring Full-text indexing, Word breaker select "Chinese (China)"

There is at least one word in the C.noise.chs file, for example:?

D. If you can modify the noise Word file correctly in Full-text search, your Full-text search does not use this file
If you are configuring a full-text search that should use this file, then
Enterprise Manager--Expand your database--right--Full text catalog--Rebuild full-text Catalogs

3. The data in the table is not retrieved after changing the
Method 1. Right--Your table--full-text Indexing table--Enable incremental population
Method 2. Right-click your table-full-text indexing table-change tracking so that subsequent modifications are automatically populated (with a certain delay)

4.sql2000 support for Full-text search of image columns