Full text search on SQL server 2008

Suppose I have a table with full-text index on column Firstname, lastname, email.

There is on row in table like FirstName LastName Email ABC DEF TAN hongtan@abc.com

Then I issued following sql:

SELECT *  FROM Person WHERE CONTAINS(*, 'hong');

I got many rows include above row.

If I issued following sql:

SELECT *  FROM Person WHERE CONTAINS(*, 'hongtan');
SELECT *  FROM Person WHERE CONTAINS(*, 'hongt开发者_C百科an@abc.com');

I got only one row include above row.

If I issued following sql:

SELECT *  FROM Person WHERE CONTAINS(*, 'hongt');
SELECT *  FROM Person WHERE CONTAINS(*, 'hongta');

I got nothing. Why for this case got nothing? I should get at least one row.

Make sure you put quotes around the word, and use the wildcard "*".

select * from Person where contains(*, '"hongt*"')

Your previous searches worked without the wildcard because it found whole words; "hong" must have been a first or last name word, and "hongtan@abc.com" is actually 3 words according to the indexing.

I learned all about full text indexes just now, thank you. :)