Full Text Search

Question

Full Text Search

JuanBob

SSCertifiable

Points: 5059
More actions
July 14, 2005 at 3:30 pm

#164352

I have Full Text Search enabled on my inventory table. I need to search based on the product description.
I would like the search power to be just like or similar to Google or other search engine. For instance, if I type in "Red Metal Wagon 5000" I want to receive all possible items sorted by RANK.
I can do this right now with ContainsTable, but I would like each word to be treated as contains such as "%red%metal%wagon%5000%'.
As it is now, if I just type in "5000" ContainsTable will NOT return anything with leading or trailing characters such as "FX5000" or "5000FX".
Any help would be greatly appreciated!

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Kane SSCrazy Points: 2810 More actions · Answer 1

JuanBob,

Could you reply with the full output of -- SELECT @@version -- on the server where your FT-enabled inventory exists? As this is most helpful information in understanding your enviroment and explaining the why ContainsTable is not returning the expected results as the OS-supplied wordbreakers are very important.

Secondly, could you post the exact ContainsTable query you are using? As the use of double quotes as well as trailing wildcards, can impact the results. Additionally, have you tried using FreetextTable? FreetextTable returns more results as it is less precise, but will provide better recall.

While SQL Server 2000 Full-text Search (FTS) is powerful and flexiable, it does not provide all the functionality that Google does. Below are some examples of using containstable and freetexttable:

use pubs

go

-- search for one word, and not another...

SELECT p.pub_id, p.pr_info, c.[rank]

from pub_info AS p,

containstable(pub_info, *, '"books" and NOT "publisher"') as c

where c. = p.pub_id

order by c.[rank]

-- FREETEXTTABLE vs. CONTAINSTABLE and

-- Error 7619 - A clause of the query contained only ignored words

select * from pub_info as A, containstable(pub_info, *, ' "how" and "do" and "I" and "add" and "a" and "listing" ') as AST

where (pub_id = '0736') and AST. = A.pub_id order by RANK desc

-- returns: Server: Msg 7619

go

select * from pub_info as A, freetexttable(pub_info, *, ' "how" and "do" and "I" and "add" and "a" and "listing" ') as AST

where (pub_id = '0736') and AST. = A.pub_id order by RANK desc

-- returns: 0 rows, and no error.

use Northwind

do

-- dynamic sql use with number search with trailing wildcard.

-- replace containstable with freetexttable and remove '+'*' and compare results.

declare @s-2 char(30)

declare @sql varchar(2000)

set @s-2 = '722'

set @sql = 'select FT_TBL.employeeId,lastname,FT_TBL.address,KEY_TBL.RANK

FROM employees as FT_TBL,

CONTAINSTABLE(employees,address,''' + '"'+''+RTRIM(@s)+''+'*'+'"'+ ''') as KEY_TBL

where FT_TBL.employeeId = KEY_TBL.'

Hope that helps,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

JuanBob SSCertifiable Points: 5059 More actions · Answer 2

Thanks John, here's the output.

I realize I'm not on the latest SP which I will do very shortly.

Here is my query after moving to FREETEXTTABLE. Same basic issues however.

SELECT itemnmbr, itemdesc, KEY_TBL.RANK

FROM tblinventory AS FT_TBL

INNER JOIN FREETEXTTABLE(tblinventory, itemdesc, 'var1') AS KEY_TBL

ON FT_TBL.itemnmbr = KEY_TBL.

ORDER BY KEY_TBL.RANK desc

Again, if we have 100 products that have 2000 in them, I want to be able to just type in 2000 and get everything that has 2000 regardless of trailing or leading characters. At the same time, I want to be able to type in "Silver Metal Shelf" and get decent results for that as well, regardless of how they show up in the itemdesc field.

Thanks again! I'm in Kirkland too... nice summer weather today, eh?

John Kane SSCrazy Points: 2810 More actions · Answer 3

You're welcome, JuanBob,

Actually, you are on the most current SP (SP4 = build 8.00.2039) and installed on Win2003 SP1. The latter is good to know becuase that means that your are using the Win2003 wordbreaker - langwrbk.dll (see http://groups.google.com/groups?q=langwrbk+infosoft for differences between Win2K and Win2003 wordbreakers.

As SQL Server 2000 Full-text Search (FTS) is a language-specific search method (vs. TSQL LIKE as a pattern search method), it cannot be all things to all people. Specificly, trailing wildcard "*" (astrick) is supportd, but not leading wildcards. So, you can do the following using the existing table authors in the pubs database:

-- Note, that all three words must exist in the address colmn to return a hit.

SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK

FROM authors as FT_TBL,

CONTAINSTABLE (authors,address, '"Bigge" and "Rd" and "10932"' ) AS KEY_TBL

WHERE

FT_TBL.au_id = KEY_TBL.

/* -- returns:

au_lname au_fname RANK

---------------------------------------- -------------------- -----------

White Johnson 64

(1 row(s) affected)

*/

-- Note, the use of the astrick after 22 & returns 22 and 2286:

SELECT FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.address, KEY_TBL.RANK

FROM authors as FT_TBL,

CONTAINSTABLE (authors,address, '"22*"' ) AS KEY_TBL

WHERE

FT_TBL.au_id = KEY_TBL.

/* -- returns:

au_lname au_fname address RANK

---------------------- -------------------- ---------------------------------------- -----------

del Castillo Innes 2286 Cram Pl. #86 64

Greene Morningstar 22 Graybar House Rd. 48

O'Leary Michael 22 Cleveland Av. #14 48

*/

Note, these results were executed using SQL 2000 - 8.00.760 on Windows NT 5.2 (Build 3790: ).

To summarize, you will have to parse the user's input and add a trailing wildcard (*), in order to use SQL FTS to find all 100 products that have 2000 in them. Additionally, to find "Silver Metal Shelf" (phrase), you will need to wrap all words within double quotes. If you want to find all there words, regardless of where they exist in the row, you will need to use the 1st above example. There is no single and simple method to achieve what you're looking for. You may also find helpful the following KB article in regards to using VBScript or JScript for the proper use of quoations: 246800 "INF: Correctly Parsing Quotation Marks in FTS Queries" at http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

Hope that helps!

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

PS: The weather was great today (Sun., 7/17/05) in Kirkland too! I plan on blogging about the weekly Jazz concerts at the Kirkland Marina soon

John T. Kane

nagendra.net Newbie Points: 8 More actions · Answer 4

Hi,

I have the similar problem, but i was using the Contains.

here i was not yet getting any result in my result set.(empty result result with out any errors)

and my select @@version output as follows:

Eaxmple query i had using

select * from example where contains (OriginZipCode ,'"1"')

example is my table with OriginZipCode (varchar(35))and one more primary key column.

this table contains the data as originzip starting with 1

can please help me for this..