CODEWITHSUNDEEP
sqlsql-server-2005
Erion
Erion

Reputation: 654

like sql freetext search

maybe this is a dumb question.

How do I achieve in sql " like '%test%' using free text ?

I thought contains and free text are equivalent to " like '%test%', plus one get's grammar check, and performance.

In my case I have : 

select * from ServiceOfferResultIndexed where contains(FirstName,'test')

which gives me 18 

rows.select * from ServiceOfferResultIndexed where FirstName like '%test%'

which gives me 229 rows.

thanks for the help.

The DB, is MS SQL 2005. I think it does support the * as postfix. It seams that if I provide '"*test"' the * is considered as a word not a wild card. Test becomes the following word.

Contains will only support "test *", where it looks for all the phrases starting with 'test' followed by any other character.

Upvotes: 0

Views: 186

Answers (1)

Code Magician
Code Magician

Reputation: 24032

Those are two different expressions. LIKE '%test%' is going to find any row where those four characters are together (e.g. testimonial, or contestant) where contains is going to match on words.

I don't know what full text engine you're using but usually wildcards are supported. For example, what happens if you use where contains(firstame, '*test*')? You'll have to consult your specific dbms' documentation for wildcards in free text search.

Upvotes: 2

Related Questions