Very Computer

Does anyone know if it is possible to do a keyword search such as the
follows:

Parameter: a long string, Keywords1, containing several keywords
Store procedure do: Retrieve in Field1 of Table1 if it contains one or
more of the words contained in Keywords1.

Seems so simple. However the challange is to retrieve the record if it
contains one or more of the words supplied. Hence, in the following
scenerio,

Table1
ID     Field1
--     ------
1      moon sun sky star
2      car boat plane

Search:
Keyword1 = "car moon"

Both records in table1 should be retrieve.

I have tried to do this in the stored procedure:

However, it only works if the keyword1 contains only 1 field. I know
that in order to accomplish the above, we need a query like:

(Keyword1_1 and Keyword1_2 are the words tokenized in Keyword1. In our
example, Keyword1_1 = "car" and Keyword1_2 = "moon")

So is it possible to do something like parsing a long string and store
the keywords in an array in stored procedure, and then construct the
SQL dynamically in stored procedure.

Any help is appreciated!!

Thanks in advance!

Stephen

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...
html/TreatYourself.asp

HTH
Jasper Smith

Hi all,

Does anyone know if it is possible to do a keyword search such as the
follows:

Parameter: a long string, Keywords1, containing several keywords
Store procedure do: Retrieve in Field1 of Table1 if it contains one or
more of the words contained in Keywords1.

Seems so simple. However the challange is to retrieve the record if it
contains one or more of the words supplied. Hence, in the following
scenerio,

Table1
ID     Field1
--     ------
1      moon sun sky star
2      car boat plane

Search:
Keyword1 = "car moon"

Both records in table1 should be retrieve.

I have tried to do this in the stored procedure:

However, it only works if the keyword1 contains only 1 field. I know
that in order to accomplish the above, we need a query like:

(Keyword1_1 and Keyword1_2 are the words tokenized in Keyword1. In our
example, Keyword1_1 = "car" and Keyword1_2 = "moon")

So is it possible to do something like parsing a long string and store
the keywords in an array in stored procedure, and then construct the
SQL dynamically in stored procedure.

Any help is appreciated!!

Thanks in advance!

Stephen

With your hint to the Full text indexing, I have tried to do the
search, and it is working pretty well. However, I have a question
regarding updating the full-text catalog.

It seems to me that every time when the source table has been changed
or updated, we need to populate the index catalog. Since the
application I am working on would have the source table changing,
updating quite often, does that mean that I have to trigger another
call to the catalog to populate it when someone changed the content on
any rows in the source table. Would that be a overhead to the process?

Thanks!

Stephen

Hello Steve,

Please  open a new post  about this question in the SQLServer:FullText
newsgroup for  better
responses.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.? 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

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

Quote:>> Depending on the size of your data this would be
>> a candidate for Full Text Indexing. Otherwise
>> you can use domething like fn_Split to parse
>> the search phrase into words and then use LIKE
>> with the separate search words.
>> However LIKE %word% searches will neccessitate
>> a table scan so performance may not be great on
>> larger data sets - hence the suggestion of FTS