What happened to my full-text search?

Question

I'm having problems with the fulltext CONTAINS statement. Here is a quick script to show what I am doing. Note that the WAITFOR line simply gives the full-text index a moment to complete the filling.

create table test1 ( id int constraint pk primary key, string nvarchar(100) not null ); insert into test1 values (1, 'dog') insert into test1 values (2, 'dogbreed') insert into test1 values (3, 'dogbreedinfo') insert into test1 values (4, 'dogs') insert into test1 values (5, 'breeds') insert into test1 values (6, 'breed') insert into test1 values (7, 'breeddogs') go create fulltext catalog cat1 create fulltext index on test1 (string) key index pk on cat1 waitfor delay '00:00:03' go select * from test1 where contains (string, '"*dog*"') go drop table test1 drop fulltext catalog cat1 

Returned result:

 1 dog 2 dogbreed 3 dogbreedinfo 4 dogs 

Why is entry # 7 'breeddogs' not coming back?

EDIT

Is there any other way to search for strings that are contained in other strings? A way that is faster than LIKE '% searchword%'?

Answer 1

Just because MS full-text search does not support suffix search - only a prefix, i.e. '*' before '* dog *' is simply ignored. This is clearly stated in Books Online By the way.

CONTAINERS can search for:

• Word or phrase.
• The prefix of a word or phrase.
• A word next to another word.
• A word spewed out from another (for example, a word drive is a flex disk drive, managed, managed, and managed).
• A word that is synonymous with another word using a thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

Where the prefix term is defined as follows:

<prefix term> :: = {"word *" | "phrase *"}

So, unfortunately: there is no way to publish a LIKE search in full-text search.