FullText

如何实现全文检索:Contains
最后附上如何设置Full-Text,
A.首前先谈谈如何使用：
简单形式：
建立测试表,数据：
CREATE TABLE [dbo].[a] (
 [a_nam] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
 [a_add] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
 [a_phone] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
insert into a select 'a','beijing 北京 bb','bb'
union select 'b','bei','bb'
--full-text栏位为a_add
1.单字
I.英文单字
Select * from a where contains(a_add,'bei')
/*
a_nam                a_add                a_phone             
-------------------- -------------------- --------------------
aa                   bei                  bb

(1 row(s) affected)
*/

select * from a where contains (a_add,'北京')
/*
a_nam                a_add                a_phone             
-------------------- -------------------- --------------------
a                    beijing 北京 bb        aa

(1 row(s) affected)
*/
2.句子必须加上“”把里面的句子括起来,例如：
select * from a where contains (a_add,'"beijing北京"')
否则出错
select * from a where contains (a_add,'beijing北京 bb')
--Syntax error occurred near 'bb'. Expected ''''' in search condition --'beijing北京 bb'.
除非里面没有空格
select * from a where contains (a_add,'beijing北京bb')

 

3.条件判断,加上And、Or，记住每个单词必须用“”引起来
Select * from a where contains(a_add,'"beijing" or "bei"')
4.英文首字加上万用字元
select * from a where contains (a_add,'"bei*"')
select * from a where contains (a_add,'"北*"')

B.异常的处理
Execution of a full-text operation failed. A clause of the query contained only ignored words.
中文版为：Contains里只包含忽略字
1.可能因为中文问题
 那么请选中表格,然后右键,选中Full-Text Index Table全文索引表,Edit Full-Text Indexing...编辑全文索引,对full-text栏位的Language For Word Breaker从英文改为中文,最后记住启动完全填充，防止停止没有执行.

2.可能是你搜索的词被sqlserver认为是通用的词
\mssql7\ftdata\sqlserver\config\
下建一上 noise.chs的把里面你的词杀掉,然后停止microsoft Seacher服务,再重新启动该服务,查询时用where contains(fieldNam,'"*你要查的词*"')即可,双引号与*号可以有,也可以无,看你用那种方式.

3.如果没有办法得到结果,又没有错误,那么请到Database->Full-text Catalogs->右键菜单->Strat Full Population,设置ok!或者右击表格,Strat Full Population,然后再执行sql