开发者

SQL Contains Question

Can someone explain this to me? I have two queries below with their results.


query:

select * from tbl where contains([name], '"*he*" AND "*ca*"')

result-set:

Hertz Car Rental

Hemingyway's Cantina


query:

select * from tbl where contains([name], '"*he*" AND "*ar*"')

result-set:

nothing


The first q开发者_JAVA百科uery is what I would expect, however I would expect the second query to return "Hertz Car Rental". Am I fundamentally misunderstanding how '*' works in full-text searching?

Thanks!


I think SQL Server is interpreting your strings as prefix_terms. The asterisk is not a plain old wildcard specifier. Fulltext and Contains are word oriented. For what you are trying to do, you would be better off using plain old LIKE instead of CONTAINS.

http://msdn.microsoft.com/en-us/library/ms187787.aspx


"*" only works as a suffix. If you use it as a prefix, the table needs to be scanned no matter what and the index is useless. At that point, you might as well do

  Select * From Table Where (Name Like '%he%') And (Name Like '%ar%')


I would try replacing * with % to see how it goes.

select * from tbl where contains([name], '"%he%" AND "%ar%"') 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜