/*Create a test environment
WINXP(SP3)+SQL SERVER2005(SP3)*/
if object_id('tb') is not null
drop table tb
go
create table tb
(id int identity(1,1),
title varchar(200),
detail varchar(1000),
constraint pk_id primary key(id) – required when building a full-text index
)
insert into tb
select 'The Rockets are about to sign a rookie shooter',' It is reported that the contract negotiations between Budinger and the Rockets were completed yesterday, and Budinger will get the same contract as Taylor. Previously, the media revealed that Taylor's contract lasts for four years and is worth a total of 10,000 US dollars, of which the first two years are guaranteed contracts. Budinger is expected to formally announce his signing to join the Rockets in the next few days. '
union all
select 'Weaver was revealed to have signed a contract with the Greek giants', 'According to International Basketball Network, former Rockets player Van Weaver has signed a contract with the Greek giants Olympiacos. Weaver received a two-year contract worth $10,000. '
union all
select 'The Spurs spent a lot of money to fight against the Lakers', 'The Spurs have added several generals during this summer offseason. Head coach Greg Veach recently revealed in an interview with Yahoo! Sports that the Spurs did not hesitate to pay the luxury tax to build a luxurious building. The lineup is designed to fight the Lakers and strive for a fifth championship. '
union all
select 'Wallace has never thought about leaving the Motor City', 'Ben Wallace has agreed to return to the Detroit Pistons and signed a $10,000-per-year contract with the Pistons at the veteran's minimum salary. According to the Daily Herald ” Columnist Mick McGraw revealed that the interior defensive player who dominated the NBA at that time never even considered leaving the Pistons. '
union all
select 'Miller is so good at fighting for love and pursuing his wife', 'For those "posteriors" who have never seen Reggie Miller create "Miller Time" with the Pacers, how should we introduce this former NBA star? Could it start with the banner pulled by a plane over Malibu Beach in Los Angeles the day before yesterday? I'm afraid no parent is willing to do this. '
union all
select 'Yao Ming: I didn't treat Shanghai as an investment project, but I was happy to do business as a good person.',' "Yao Mi" said: Yao Ming, who has no shortage of advertising effects, acquired the Shanghai Oriental Basketball Club, which is on the verge of extinction, which shows that he really wants to make a living for the former The parent team does something. '
union all
select 'The Rockets lost to the Wizards to end the year's record', 'The Yao-Mai combination was in bad shape in this campaign. Yao Ming scored a rebound and a block on a shot, and McGrady scored a rebound and an assist on a shot. The two of them teamed up to get a rebound. Jamieson had two assists and two blocks. '
Step 1: Enable full-text indexing of the database
sp_fulltext_database enable --Enable full-text indexing of the database
go
Step 2: Create a full-text catalog
create fulltext catalog tb_fulltext
in path N'D:Program FilesMicrosoft SQL Server2005MSSQL.1MSSQLFTData'
with accent_sensitivity =on -- distinguish accents
authorization dbo;--the owner of the full-text catalog
Step 3: Create full-text index
create fulltext index on tb
(title,detail)
key index pk_id --Specify the index column. In order to improve performance, it is best to use a clustered index
on tb_fulltext
with change_tracking auto -- Automatically update the full-text index when data is modified in the associated table.
Step 4: Query example:
select * from tb
where contains((title,detail),'Yao Ming')
Query syntax:
1. Search for specific words: contains(detail,'Yao Ming')
2. Search for a specific phrase: Use "" to enclose the phrase in double quotes, contains(detail,'Yao Ming Shanghai')
3. Search for words and phrases from multiple columns: contains((title,detail),'Yao Ming Shanghai')
4. Search for words or phrases starting with the specified text: contains(detail,'"Yao Ming*"')
If the text and the asterisk are not enclosed in double quotes, full-text search will treat the asterisk as one character. If the search is for a phrase, each word within the phrase is considered a prefix. contains(detail,'"Yao Ming Shanghai*"') will return the results where the first word starts with Yao Ming and the second word starts with Shanghai
5. Search for variations of specific words: contains(detail,'formsof(inflectional,ride)')
Will return rows containing ride, rides, riding, ridden in the table
6. Search for words or phrases that are close to another word or phrase: contains(detail,'Yao Ming near Shanghai')
7. Words or phrases that use weighted values: contains(description,'isabout(performance weight(.8),comfortable weight(.4),smooth weight(.2))')
Weight assigns a weighting value between 0.0 and 1.0 to each word or phrase.
8. Use multiple search conditions: contains(detail,'"Yao Ming" or "Shanghai"')
contains(detail,'"Yao Ming" and "Shanghai"')
contains(detail,'"Yao Ming" and not "Shanghai"')
9. You can also use variables in contains
10. Search for synonyms contains(title,'formsof(thesaurus,on)')
【test】
In the above example, execute
select * from tb
where contains(title,'Shanghai')
result:
id title detail
6 Yao Ming: He didn’t regard Shanghai as an investment project. He was happy to be a good businessman and became a good person. "Yao Mi" said: Yao Ming, who has no shortage of advertising effect, acquired the Shanghai Oriental Basketball Club, which was on the verge of extinction, which shows that he really wants to do something for his former parent team. matter.
But execute:
select * from tb
where contains(title,'上')
The result is an empty set.
Reason: When performing a full-text index query, "上" is an interference word in Simplified Chinese, that is, it will be ignored during the query; in addition, when establishing a full-text index, a phrase is generally used as an index item instead of a single word.
If you want to perform this query again and want to find out the results of the first query, you can modify the thesaurus of the full-text index.
The path where the noise words and synonyms files exist:
D:Program FilesMicrosoft SQL Server2005MSSQL.1MSSQLFTData. Each file name should be easily identifiable.
Here is a brief introduction to how to modify the thesaurus:
1. Use Notepad to open tschs.xml, which is a synonym library for Simplified Chinese
<XML ID="Microsoft Search Thesaurus">
<!-- Commented out
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>Internet Explorer</sub>
<sub>IE</sub>
<sub>IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
</thesaurus>
-->
</XML>
explain:
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
is a substitute word, that is, when querying W2K, it will be automatically replaced by Windows 2000 for query.
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
It is a synonym library, that is, when querying run, jog will also be searched.
In this example add:
<expansion>
<sub>Up</sub>
<sub>Shanghai</sub>
</expansion>
2. Remove the comment line starting at the end:
<!-- Commented out
-->
3. The final result is:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>Internet Explorer</sub>
<sub>IE</sub>
<sub>IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
<expansion>
<sub>Up</sub>
<sub>Shanghai</sub>
</expansion>
</thesaurus>
</XML>
3. Execute the query statement:
select * from tb
where contains(title,'formsof(thesaurus,on)')
You can see that the query results are the same as contains(title,'Shanghai')