SQL freetext() not working on some columns

Question

I have a table which contains 7 columns and I have created a full text index on the table. However I noticed that a search using freetext() does not return any rows on 2 of the columns Its returns rows on other columns.

Here is my query

select * from dbo.ModelCategoryValues
where freetext(economyvalues,'24,29')
and freetext(featurevalues,'10')
and freetext(pricerangevalues,'15')
and freetext(performancevalues,'18,20')
and freetext(economyvalues,'22,24')
and freetext(usevalues,'28')

This returns expected results

However when I run the below no rows are returned

select * from dbo.ModelCategoryValues
where freetext(cartypevalues,'1')

I can see rows corresponding to the above data. I have tried everything from re-populating index to re-creating it but no success.

Here is the db diagram https://www.dropbox.com/s/hbjhucghck8sfpz/Capture.PNG?dl=0 — Poornima Nayar, Sep 16 '14 at 09:57
Obvious question - but is cartypevalues actually in the index? — Allan S. Hansen, Sep 16 '14 at 10:32
Here is the entire db schema https://www.dropbox.com/s/hbjhucghck8sfpz/Capture.PNG?dl=0 — Poornima Nayar, Sep 16 '14 at 10:34
yes, all the columns except for the variantid and categoryid are in the index.The index is working well on all other columsn except for carseatvalues and cartypevalues columns. Using contains() does not work either — Poornima Nayar, Sep 16 '14 at 11:05
here is the data in the table. I want to be able to pass one or many of atleast one column and do the search https://www.dropbox.com/s/uqoezvnrvp4u6pi/Capture1.PNG?dl=0 — Poornima Nayar, Sep 16 '14 at 11:16

score 0 · Answer 1 · answered Aug 10 '17 at 06:36

0

FREETEXT by default excludes the records having "STOPLIST" Values. To resolve the problem, set the StopList to 'OFF' using following query :-

ALTER FULLTEXT INDEX ON DealerSearch SET STOPLIST = OFF

answered Aug 10 '17 at 06:36

Abhishek Chandel

134
1
4

SQL freetext() not working on some columns

1 Answers1