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 He

Solution 1:

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

Solution 2:

"*" 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*FromTableWhere (Name Like'%he%') And (Name Like'%ar%')

Solution 3:

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

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