search engine in SQL....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lobrys - 06 Jul 2004 17:35 GMT

Hi

here is a simple question :

How to simply create a good search engine with the FULLTEXT functionnality
of SQL SERVER?

In fact, I have done something....
but , I want that the search look INTO words
for exemple, i I search for the word "SOFT", ....the search engine must
return MICROSOFT, SOFTWARE, SOFT, etc......

here is my SELECT request :

SELECT tbl_prod_key.RANK, p.produit_id
FROM product AS p
INNER JOIN FREETEXTTABLE (product, *, 'FULL') AS tbl_prod_key ON
p.prod_id = tbl_prod_key.[KEY]
ORDER BY tbl_prod_key.RANK DESC

THANKS!
bye!

syl

Reply to this Message

John Kane - 07 Jul 2004 03:34 GMT

Syl,
Simple question, but difficult answer... The short answer is no, this is not
possible. The longer answer is that SQL Server Full-text Search with the
MSSearch service supports wildcard search (using * or asterisk) as a "word
suffix", such as "fish*" and not as a "word prefix" such as "*fish". If you
want a search engine to "look into" words, you might want to review the
T-SQL LIKE command and using PATINDEX, see to review the BOL titles "Pattern
Matching in Search Conditions" and "PATINDEX" as well as "Comparing
CHARINDEX and PATINDEX" as SQL Full-text Search (FTS) is not designed for
string pattern matching. FTS is more of a word-based search method vs. T-SQL
LIKE's pattern-string method. Bellow is an Patindex T-SQL example:

use Northwind
select Description from Northwind.dbo.Categories
where patindex('%[bB]read%',description) > 0
and patindex('_[^e]%',description) = 1
/* -- returns:
Description
---------------------------------------
Breads, crackers, pasta, and cereal
(1 row(s) affected)
*/

Regards,
John

> Hi
>
[quoted text clipped - 20 lines]
>
> syl

Reply to this Message