CODEWITHSUNDEEP
sqlsql-serverfull-text-searchcontains
Michael Krauklis
Michael Krauklis

Reputation: 3954

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

Upvotes: 3

Views: 514

Answers (3)

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16259

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

Upvotes: 5

Stu
Stu

Reputation: 15769

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

Upvotes: 4

C.S.-Lecours
C.S.-Lecours

Reputation: 33

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

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

Upvotes: -1

Related Questions