FullText Question

Question

FullText Question

olsentimothy03

Old Hand

Points: 329
More actions
March 18, 2010 at 5:27 pm

#139425

Good day Forumites,
I have a full text search set up on the "Goods" table with some columns below, the fulltext search columns are also below.
Goods - GoodsID, GoodsCategoryID, GoodsBarCode, NameOfGoods
GoodsCategory - GoodsCategoryID, GoodsCategory
Fulltext Search columns - GoodsBarCode, NameOfGoods from Goods Table
The following code works fine without the where clause. When I include the where clause, it returns nothing. There are goods in this category that have 'no' in them so this is not correct. I didnt provide a table structure because of the full text but is there something im missing in the code?
SELECT goods.goodsbarcode,
goods.nameofgoods
FROM goods
JOIN freetexttable (dbo.goods, (nameofgoods,goodsbarcode), 'no', 5) AS gt
ON
goods.goodsid = gt.
WHERE goodscategoryid in (10,20,30,40)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

Your FREETEXTTABLE query returns just the top 5 matches by rank. There is no guarantee that these five will match the condition in your WHERE clause.

Also, the search term 'no' will be wordbroken, stemmed, and passed through the thesaurus. If you just want to do a simple word search, use CONTAINSTABLE instead.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

I agree with Paul. Eliminate the 5 from your FREETEXTTABLE query, and then implement the containstable.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150457 More actions · Answer 3

Please let me know if my explanation wasn't clear, and I will attempt to clarify.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

olsentimothy03 Old Hand Points: 329 More actions · Answer 4

Thanks for the response guys. I really appreciate it. So to use the containstable will just be as easy as replacing the word freetexttable with containstable and removing the '5'. Ill try that and get back to you guys. Thanks guys.

Thanks Paul for your sincere good heart and not to mention the speed at which you reply people's questions on here..

Mauve SSChampion Points: 11316 More actions · Answer 5

"no" is a Stop (noise) word and will not be indexed. Which means that it cannot be searched.

My strong opinion: Eliminate the Stop (noise) words. You'll be better off.

Also scan the forum threads for my handle and/or full-text. There are lots of postings.

We are a SaaS company using full-text extensively. The SQL Server functions that we use are CONTAINS and CONTAINSTABLE (when we need to rank hits). We also utilize the stemming feature (FORMSOF INFLECTION) with languages.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Paul White SSC Guru Points: 150457 More actions · Answer 6

Mauve (3/24/2010)
"no" is a Stop (noise) word and will not be indexed. Which means that it cannot be searched. My strong opinion: Eliminate the Stop (noise) words. You'll be better off. Also scan the forum threads for my handle and/or full-text. There are lots of postings. We are a SaaS company using full-text extensively. The SQL Server functions that we use are CONTAINS and CONTAINSTABLE (when we need to rank hits). We also utilize the stemming feature (FORMSOF INFLECTION) with languages.

In the first post, it was stated that the query worked fine without the WHERE clause, therefore 'no' has already been removed from the noise word list.

The correct syntax is FORMSOF(INFLECTIONAL, ...)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi