How to search for Keywords? Thank You.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Miguel Dias Moura - 10 Dec 2004 20:04 GMT

Hello,

I have full text in a MS SQL database table.
I also have a string "Search" which contains all the keywords passed in
the URL to the page.aspx. I want to use the keywords.

What I have now is this:
SELECT *
FROM dbo.documents
WHERE CONTAINS (*, '"ASP*" or "BOOK*"')

Of course this only looks only for 2 well defined words.
Can you tell me how can I use the keywords instead?

Thanks,
Miguel

Reply to this Message

John Kane - 11 Dec 2004 07:14 GMT

Miguel,
Could you post the full output of -- SELECT @@version -- as this is very
helpful information in not only understanding your environment, but in
troubleshooting SQL FTS issues.

If I understand your request, you're interested in passing the ASP (.NET?)
string "Search" which is the users search input to search your website.
Correct? If so, then you want to pass these search keywords into the
<search_condition> of the CONTAINS clause. Correct? Then you may be
interested in this very good tutorial - "Integrating User Search with ASP
and SQL Server Full-Text Search" (By Robert Dominy) at
http://www.15seconds.com/issue/010423.htm.

If this is not what you're looking for could you provide a more detailed
explanation?

Thanks,
John

> Hello,
>
[quoted text clipped - 12 lines]
> Thanks,
> Miguel

Reply to this Message

Miguel Dias Moura - 11 Dec 2004 11:37 GMT

Hi,

I have a page with a search form where keywords are submitted.
Consider I write the keywords 'asp' and 'book'.
The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String =
Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP') AND CONTAINS (*, 'BOOK')

This SQL looks only for 'ASP' and 'BOOK' words.
I need to look for the Keywords included in the string keywords().

If I am not wrong I need to do something like:
WHERE CONTAINS (*, keyword(1)) AND CONTAINS (*, keyword(2))... AND
CONTAINS (*, keyword(i))

This is what I don't know how to do.

Thank You,
Miguel

"John Kane" <jt-kane@comcast.net> wrote in message
news:jt-kane@comcast.net:

> Miguel,
> Could you post the full output of -- SELECT @@version -- as this is very
[quoted text clipped - 34 lines]
> > Thanks,
> > Miguel

Reply to this Message

John Kane - 11 Dec 2004 18:24 GMT

Miguel,
Now, I understand better what you need to know. I believe that this
functionality is best achieved in a stored procedure, for example using the
Pubs database table pub_info and its text column pr_info:

DROP PROCEDURE sp_FTSearchPubsInfo
go
CREATE PROCEDURE sp_FTSearchPubsInfo ( @vcSearchText varchar(7800))
AS
declare @s as varchar (8000)
set @s='select pub_id, pr_info from pub_info where
contains(*,'+''''+@vcSearchText+''''+')'
exec (@s)
go

Exec sp_FTSearchPubsInfo '"book*"'
-- returns: simple use to search for book, books, booking

EXEC sp_FTSearchPubsInfo '("book*") or ("publish*")'
-- returns: all eight rows in the pub_info table.

EXEC sp_FTSearchPubsInfo '(("book" or "books") and ("publish" or
"publisher"))'
-- returns: only the two rows that have these specific words.

You can then use this very flexible approach to pass your search keywords -
any number of them, including wildcards (trailing "*" asterisks), and use
"(" and ")" to enforce order. A more complex query would look like this:

EXEC sp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or "wastepaper")
or
("recycle* paper") or (("paper slurry") and ("paper sludge")) or
("biodegrad* paper") or
("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
"chemical*"))'
-- returns: 0 rows because none of these words are in the pr_info column of
the table pub_info.

EXEC sp_FTSearchPubsInfo '("books") or ("testing") and ("for")'
-- returns: 2 rows, even though "for" is a noise word in the US_English
noise word file noise.enu

However, if you change the above query to:
EXEC sp_FTSearchPubsInfo '(("books") or ("testing")) and ("for")'
it returns the error Msg 7619 - "Execution of a full-text operation failed.
A clause of the query contained only ignored words". So, keep in mind that
you may want to use some form of either client-side or server-side editing
of the search keywords to remove the noise words that are in your
language-specific noise word file under \FTDATA\SQLServer\Config. You may
also want to remove some of these noise words as well, but not all of them
to create an empty (0 length) file as there is a bug with using an empty
noise word file. You may also want to review KB article 246800 (Q246800)
"INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

Let me know if you find this helpful!
Thanks,
John

> Hi,
>
[quoted text clipped - 68 lines]
> > > Thanks,
> > > Miguel

Reply to this Message