SQL Free Text And Like

Question

If I use like '%fish%' the following is returned

AQUARIAN GOLDFISH FLAKES

but if I use Contains([Description],' "fish*" ' ) it isn't is there something I can do?

Basically I want to return anything that has the word fish in it anywhere.

Answer 1

What about

 CONTAINS([Description], '"fish"')

or

 FREETEXT([Description], 'fish')

Does that give you anything?

SQL Server Fulltext indexing does not support searching for an expression with a leading wildcard, e.g. you cannot go a CONTAINS([Description], '"*fish*"') or something like that :-(

What you could also do is define your own synonym for fulltext search, e.g. define "goldfish" to be a synonym for "fish" - than you should be able to search for just "fish" and also find "goldfish".

SELECT (list of fields) FROM YourTable
WHERE CONTAINS([Description], 'FORMSOF(THESAURUS, fish)')

Check out Understanding Full-Text Indexing in SQL Server - there's a good section a bit down in the article on modifying the fulltext thesaurus.

Marc