July 28, 2011 at 2:40 am
I have following data in "title" column. The column "title" is included in my FullText search.
I have following data in title column:
webcam with laptop
webcam with charger
webcam and laptop
webcam with cable
webcam and mouse
webcam only
webcam
laptop and mouse
I am searching "webcam with" and using following query:
SELECT [Id], RANK FROM SearchLookup AS FT_SearchInfo WITH (NOLOCK)
INNER JOIN CONTAINSTABLE(SearchLookup, (Title) , '"webcam with"') AS KEY_TBL
ON FT_SearchInfo.ID = KEY_TBL.
the results are same without doble quotes to find exact match as follows
SELECT [Id], RANK FROM SearchLookup AS FT_SearchInfo WITH (NOLOCK)
INNER JOIN CONTAINSTABLE(SearchLookup, (Title) , ("mashup*") OR ("with*")) AS KEY_TBL
ON FT_SearchInfo.ID = KEY_TBL.
both are returning same results. I want when a "with" keyword is used to search exact keyword using double quotes then it should searched exact match in title which is not happening...
the results are:
webcam
webcam only
webcam and mouse
webcam with laptop
webcam with charger
webcam and laptop
webcam with cable
but it should be:
webcam with laptop
webcam with charger
webcam with cable
plz. help....
July 28, 2011 at 3:05 am
Check your STOPLIST on the Resource database, I suspect WITH may be in there.
July 28, 2011 at 3:51 am
I used stopList and added keyword "with" but no luck, then i removed it and now there is no any stopList on my FullText stop list.
Shamshad Ali.
July 28, 2011 at 5:36 am
Have you rebuild the fulltest index after removing WITH from stoplist?
You can try to diassociate stoplist from Index:
ALTER FULLTEXT INDEX ON table_name SET STOPLIST OFF
What is compatibility level of your database?
July 28, 2011 at 6:36 am
SQL Server 2008 (100) R2 Enterprise Edition.
I re-build the fulltext index
alter fulltext index on SearchLookup
set stoplist StopWith;
where StopWith is the stopList name and i have added "with" as stop word. Also if i search only "with" keywork the result is 0.
Shamshad Ali.
July 28, 2011 at 6:44 am
You shouldn't have this word in the list! It works other way arround!
Try
ALTER FULLTEXT INDEX ON table_name SET STOPLIST OFF
then rebuild the index.
July 28, 2011 at 6:49 am
yes, its working now. Thanks
1- What are the pros & cons of this change?
2- Should I remove the stopList which is StopWith as mentioned above? or it is required and part of solution.
3- if there are title such as sellable and i put keyword seller then would it bring results or NOT as it was working before?
Shamshad Ali.
July 28, 2011 at 7:06 am
Shamshad Ali (7/28/2011)
yes, its working now. Thanks1- What are the pros & cons of this change?
2- Should I remove the stopList which is StopWith as mentioned above? or it is required and part of solution.
3- if there are title such as sellable and i put keyword seller then would it bring results or NOT as it was working before?
Shamshad Ali.
1. P: It will search for any word. C: It will search for any word.
2. Depends what do you want. If you want to ignore noise words then add them into stop list and assign it to index and full text search will ignore them.
3. See point 2.
By default, fulltext index uses SYSTEM stoplist. You can change it or swithc it off completely as you did.
July 28, 2011 at 7:41 am
Thanks alot. Your help and sharing knowledge is highly appreciated ...
Shamshad Ali.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply