Full text search on SQL server 2008

Question

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 [email protected]

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(*, '[email protected]');

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.

Answer 1

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 "[email protected]" is actually 3 words according to the indexing.

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