Shulou(Shulou.com)05/31 Report--

SQLServer how to full-text search full-text grammar, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

There are two search methods for sql server full-text search, one is contains, the other is freetext. The former is contained, similar to like'% keyword%', while the latter is to search for each word after segmenting a paragraph of text. Specific syntax: contains: SELECT field 1, field 2 FROM table name WHERE contains (field,'"word one" word two "') sort SELECT field 1 according to the similarity of the search results, field 2 FROM table name inner join containstable (table name, field, 'word one" word two ", 10) 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 Sort SELECT field 1 according to the similarity of the search results, field 2 FROM table name inner join freetexttable (table name, field, 'word two', 10) as k on table name. ID = k. [key] order by k.RANK DESC the 10 of freetexttable or containstable above takes 10 pieces of data

A recent search for full-text search found some problems, which are now summarized as follows: full-text indexing and query concepts (from SQL online help) the main design requirement for full-text indexing, query, and synchronization is that there is a unique full-text key column (or single-column primary key) on all tables registered for full-text search. The full-text index tracks the important words used and their location. For example, suppose you have a full-text index on the DevTools table. The full-text index may indicate that the word Microsoft was found at the 423th and 982nd words in the Abstract column, in a row associated with ProductID6. The index structure supports effective retrieval of all items containing indexed words, as well as advanced retrieval operations, such as phrase retrieval and proximity retrieval. To prevent full-text indexing from becoming bloated because it contains many words that are not helpful to retrieval, additional words such as a, and, is, or the are ignored. For example, specifying "theproductsorderedduringthesesummermonths" is the same as specifying "productsorderedduringsummermonths". Lines with both strings are returned. Lists of distractors in multiple languages are provided under the directory\ Mssql\ Ftdata\ Sqlserver\ Config. This directory is created when you install Microsoft ®SQLServer ™with full-text search support, along with interfering word files. Interfering word files can be edited. For example, system administrators in high-tech companies can add the word computer to their interfering vocabulary. (if you edit an interfering word file, you must repopulate the full-text catalog before the changes take effect. The following table shows the interfering word file and its corresponding language. Interfering 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 Noise.jpn Japanese Noise.kor Korean Noise.nld Dutch Noise.sve Swedish when processing full-text query The retrieval engine returns the key value of the row that meets the check condition to MicrosoftSQLServer. For example, there is a SciFi table where the Book_No column is the primary key column. Book_NoWriterTitle-A025AsimovFoundation'sEdgeA027AsimovFoundationandEmpireC011ClarkeChildhood'sEndV109VerneMysteriousIsland assumes that you want to use a full-text search query to find the title of a book containing the word Foundation. In this example, the values A025 and A027 will be obtained from the full-text index. SQLServer then responds to the query with these key values and other columns of information. The following table shows the language used to store full-text indexed data. These languages are based on the Unicode collation locale identifier selected during SQLServer installation. Unicode collation locale identifier language used for full-text data storage-traditional Chinese phonetic symbols (Taiwan) traditional Chinese Pinyin simplified Chinese strokes (Taiwan) traditional Chinese strokes Dutch English (UK) English (UK) French French General Unicode English (USA) German phone Book German Italian Japanese Unicode Japanese Korean Unicode Korean Spanish (Modern) Spanish Swedish / Finnish Swedish all other Unicode collation locale identifier values that are not in this list are mapped to the use of spaces The character breaker and stem separator of a neutral language word that separates words. Describes the Unicode collation locale identifier setting for all data types that can be indexed full-text (such as char, nchar, and so on). If the language type that is set for the sort order of columns of type char, varchar, or text is not the Unicode collation locale identifier language, the Unicode collation locale identifier value is still used when full-text indexing and querying columns of type char, varchar, and text. Create a full-text index (take index image as an example, other types of fields are roughly the same) title full-text index image column, full-text strategy! Author pengdali [original] keyword full-text index image today "once in a century" power outage, read a day. After doing a full-text index in the evening, I decided to post my experience. I will try my best to write it in detail. We will study together. Welcome to correct me. 1. Start the MicrosoftSearch service start menu-- > SQL program group-- > service manager-- > drop down-- > MicrosoftSearch service-- > start it 2.\ MicrosoftSQLServer\ MSSQL\ FTDATA\ SQLServer\ Config\ directory to create a non-empty noise.chs file, non-empty noise.chs file, and some people say it is an empty noise.chs file, but I write a few useless letters into it every time. 3. Create environment. Open query Analyzer-- > execute the following script:-createdatabasetest--- create test database usetest--- Select test database createtabledali (IDintnotnullprimarykey,MyImageimage,FileTypevarchar, FileNmaevarchar)-create dali table-- three Id,MyImage,FileType columns in the dali table are required Because to index an image column, there must be a primary key column An image column, a column that holds file types-- we know that file types are distinguished by extensions in windows systems, so the FileType column is the extension used to put files-sp_fulltext_database'enable'-- enables the database sp_fulltext_catalog'My_FullDir' for full-text indexing. Create'--- creates a full-text catalog called My_FullDif declare@Keysysname Select@Key=c.namefromsyscolumnsa,sysconstraintsb,sysobjectscwherea.id=object_id ('dali') anda.name='ID'anda.id=b.idandb.constid=c.idandc.namelike'PK%'execsp_fulltext_table'dali','create','My_FullDir',@Key---- these two sentences are for full-text indexing, mark the table sp_fulltext_column'dali','MyImage','add',0x0804,'FileType'--- this sentence specifies that the MyImage column is a full-text indexed column FileType is the type column-4. Under disk c, there are a word file with the extension doc, an excel file with the extension xls, a web file with the extension htm, and four images with the extension bmp You can put it in according to the actual situation! 5. Insert data to create the following stored procedure-CREATEPROCEDUREsp_textcopy@srvnamevarchar (30), @ loginvarchar (30), @ passwordvarchar (30), @ dbnamevarchar (30), @ tbnamevarchar (30), @ colnamevarchar (30), @ filenamevarchar (30), @ whereclausevarchar (40) @ directionchar (1) AS/* this is to insert a file into the database using the textcopy tool If there is a foreground tool, you can use the foreground development tool to insert the file. Here to demonstrate * / DECLARE@exec_strvarchar (255) SELECT@exec_str='textcopy/S'+@srvname+'/U'+@login+'/P'+@password+'/D'+@dbname+'/T'+@tbname+'/C'+@colname+'/W "'+ @ whereclause+'" / F "'+ @ filename+'" /'+ @ directionEXECmaster..xp_cmdshell@exec_str -- insertdalivalues (1B0x Doc',' powerful doc')-the second column is 0x, which is a hexadecimal number corresponding to the image column, is necessary, do not write null, the third column is the file type Both the extension sp_textcopy' your server name', 'sa',' your password', 'test','dali','MyImage','c:\ powerful doc.doc','whereID=1','I'- parameters are: instance name, user name, password, database name, table name, image column name, path and file name, conditions (you must make sure it selects only one row) I-insertdalivalues (2pc0x _ mp' 'Picture') sp_textcopy' your server name', 'sa',' your password', 'test','dali','MyImage','c:\ picture .bmp', 'whereID=2','I'-- attention condition is ID=2insertdalivalues (3pc0x, sa',' your password,' test','dali','MyImage','c:\ Excel file .xls', 'whereID=3' Sp_textcopy' your server name, 'sa',' your password', 'test','dali','MyImage','c:\ webpage .htm', 'whereID=4','I'-- attention condition is the statement above ID=4-, make sure the type is the same. The path is correct and the condition is only correct. 6. Populating the full-text index sp_fulltext_table'dali','start_full'--- the first parameter is the table name. The second parameter is to start the full-text index of the table fully populated 7, you can start your experiment select*fromdaliwherecontains (MyImage,'J teacher') select*fromdaliwherecontains (MyImage,' teacher')-END- debugging environment: SQLServer2000 Enterprise Edition, Windows2000 advanced server full-text index several questions: 1. An error occurred during the search: server: message 7619, level 16, status 1, line 2 query clause contains only ignored words. In this case, modify the interfering word list file for the corresponding language under\ Mssql\ Ftdata\ Sqlserver\ Config. The interference word file has been modified, and the above problems still occur when querying Chinese. First check whether your SQL has installed the latest patch, check by running in query Analyzer: select@@version if the version number is less than 8.00.760, it means that you have not installed the patch for sp3. Download: http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=9032f608-160a-4537-a2b6-4cb265b80766 Note: after downloading, the execution is decompressed. Executing setup.bat in the unzipped directory is the real installation b. When configuring full-text indexing, the word breaker selects at least one word in the "Chinese (China)" c.Noise.chs file, for example:? d. If you can modify the interfering word file normally during full-text search, indicating that this file is not used in your full-text search, then in Enterprise Manager-- expand your database-- right-click on the full-text catalog-- rebuild the full-text catalog 3. Method 1 cannot be retrieved after the data in the table has changed. Right-click your table-full-text index table-enable incremental fill method 2. Right-click your table-- full-text index table-- change tracking, so that future modifications will be automatically populated (with a certain delay) 4.sql2000 to support full-text retrieval of image columns.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.