SQL Contains Question

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 query 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!

Answer 1

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

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

Answer 2

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

Answer 3

"*" 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%')