SQL Server 2008 full-text index issue

Question

I have a TableA as specified below in which a full-text index is specified for column Name

TableA

Id (PK)
Name (TXI)

The table has the following records:

ingrid
neville
ned
audry
inga

I then run the following query against the table:

SELECT * FROM TableA WHERE CONTAINS(Name,'"n*"');

The result of the query is as follows:

Id         Name
1          ingrid 
2          neville
3          ned

Why is it returning the first record even though it doesn't starts with 'n'?

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Jun 02 '11 at 16:37

score 0 · Answer 1 · answered May 08 '12 at 21:02

Your query works for me....:

TRUNCATE TABLE FTS_Demo_2

INSERT  FTS_Demo_2 (Document) VALUES ('ingrid')
INSERT  FTS_Demo_2 (Document) VALUES ('neville')
INSERT  FTS_Demo_2 (Document) VALUES ('ned')
INSERT  FTS_Demo_2 (Document) VALUES ('audry')
INSERT  FTS_Demo_2 (Document) VALUES ('inga')

/* WAIT FOR AUTO POPULATION TO POPULATE */

SELECT  * FROM FTS_Demo_2 WHERE CONTAINS (Document, '"n*"')

Results:

RecordID    Document
----------- --------------------
2           neville
3           ned

(2 row(s) affected)

Maybe it's a problem with your FTS Index or Catalog? What are your column types? I made the column in this example NVARCHAR(MAX), and this is the catalog properties:

enter image description here

score 0 · Answer 2 · answered Jun 02 '11 at 16:36

0

Try:

SELECT * FROM TableA WHERE CONTAINS(Name,'n*');

answered Jun 02 '11 at 16:36

Sankar Reddy

1,499
9
10

Doing that will get me no records – Luis Jun 02 '11 at 16:54

score 0 · Answer 3 · answered Jun 02 '11 at 19:24

0

It's possible your full-text index is out of date. Does rebuilding it fix your problem?

answered Jun 02 '11 at 19:24

Jim B

8,344
10
49
77

score 0 · Answer 4 · answered Jun 02 '11 at 21:01

By following the Jim B suggestion try to update your catalogue by:

ALTER FULLTEXT CATALOG TableA REORGANIZE

then

SELECT * FROM TableA WHERE CONTAINS(Name,'"n*"');

Another tip: The purpose of Full-Text is searching for WORDS inside a text so i dont know if you are testing the full-text but to get what you are looking for, they right way is the operator LIKE as:

SELECT * FROM TableA WHERE Name LIKE 'n%';

SQL Server 2008 full-text index issue

4 Answers4