Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > boolean search expressions?

Reply
Thread Tools

boolean search expressions?

 
 
David
Guest
Posts: n/a
 
      02-08-2004
Hi,

I'm trying to add a search facility to a page that looks for matches in one,
other or both memo fields of a database. The code below works fine if the
visitor types in one word, or the term just happens to exist in one of the
queried fields.

What I'd really like is for a visitor to type in an expression, or query in
the same format as you would use in a search engine and it would find
appropriate matches. Any ideas how I can modify the code below to do this?

Thanks

set rsData = con.execute("select topic, title, ID, description from journals
where articletext LIKE '%" & searchstring & "%' OR description Like '%" &
searchstring & "%' ORDER BY dateno DESC")


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      02-08-2004
David wrote:
> Hi,
>
> I'm trying to add a search facility to a page that looks for matches
> in one, other or both memo fields of a database. The code below
> works fine if the visitor types in one word, or the term just happens
> to exist in one of the queried fields.
>
> What I'd really like is for a visitor to type in an expression, or
> query in the same format as you would use in a search engine and it
> would find appropriate matches. Any ideas how I can modify the code
> below to do this?
>
> Thanks
>
> set rsData = con.execute("select topic, title, ID, description from
> journals where articletext LIKE '%" & searchstring & "%' OR
> description Like '%" & searchstring & "%' ORDER BY dateno DESC")


"Memo" implies Access, right? Please don't make us guess. Always tell us the
type and version of database you are using. It is almost always relevant.

Anyways, I think we need to see specs for the expression syntax you want the
visitor to use. Depending on the search engine you are talking about, the
syntax can be very simple, or very complex. So tell us what you mean by "the
same format as you would use in a search engine".

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
David
Guest
Posts: n/a
 
      02-08-2004

> "Memo" implies Access, right? Please don't make us guess. Always tell us

the
> type and version of database you are using. It is almost always relevant.


I'm sorry - I forgot that bit. Yes, the database is Access (office2000
version)
>
> Anyways, I think we need to see specs for the expression syntax you want

the
> visitor to use. Depending on the search engine you are talking about, the
> syntax can be very simple, or very complex. So tell us what you mean by

"the
> same format as you would use in a search engine".


Ideally I'd like the visitor to be able to type in an expression such as:-
model railway in england
into the search box. If they want to type in all the " ' + type characters
you can use in a search engine I'd strip them from the querystring.

So, if the visitor types in model railway in england, then the search would
look in both memo fields to see if any of the words in that expression
appeared in either field of the database.

I'd go down the route of the code stripping common words like "of at and in"
etc from the querystring.

Hope this provides more information. I don't want a fully featured search
engine - just the ability to see if any of the words in a search string are
in the database.

Thanks


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      02-08-2004
David wrote:
>> "Memo" implies Access, right? Please don't make us guess. Always
>> tell us the type and version of database you are using. It is almost
>> always relevant.

>
> I'm sorry - I forgot that bit. Yes, the database is Access (office2000
> version)
>>
>> Anyways, I think we need to see specs for the expression syntax you
>> want the visitor to use. Depending on the search engine you are
>> talking about, the syntax can be very simple, or very complex. So
>> tell us what you mean by "the same format as you would use in a
>> search engine".

>
> Ideally I'd like the visitor to be able to type in an expression such
> as:- model railway in england
> into the search box. If they want to type in all the " ' + type
> characters you can use in a search engine I'd strip them from the
> querystring.
>
> So, if the visitor types in model railway in england, then the search
> would look in both memo fields to see if any of the words in that
> expression appeared in either field of the database.
>
> I'd go down the route of the code stripping common words like "of at
> and in" etc from the querystring.
>
> Hope this provides more information. I don't want a fully featured
> search engine - just the ability to see if any of the words in a
> search string are in the database.
>
> Thanks


That's a relief: you're not going to allow the use of "exclusion words".

You're options are very limited, since you are not limiting the number of
words being typed. I see no way to avoid concatenating a potentially long
dynamic sql statement, which will probably perform horribly. If you were
using SQL Server, you could use the Full Text Search functionality, but ....

Anyways, you'll need to carry out your plan to break up the search string
into discrete words (you can use Split for this):
wordarray = split(searchstring, " ")

and then loop through the array, adding an OR clause to the query for each
word in the array:
where articletext LIKE '%" & wordarray(0) & "%' OR description Like '%" & _
wordarray(0) & "%' OR articletext LIKE '%" & wordarray(1) & _
"%' OR description Like '%" & wordarray(1) & "%' etc.

Did I mention that this will probably perform horribly?

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      02-08-2004
Bob Barrows wrote:

>
> Anyways, you'll need to carry out your plan to break up the search
> string into discrete words (you can use Split for this):
> wordarray = split(searchstring, " ")
>
> and then loop through the array, adding an OR clause to the query for
> each word in the array:
> where articletext LIKE '%" & wordarray(0) & "%' OR description Like
> '%" & _ wordarray(0) & "%' OR articletext LIKE '%" & wordarray(1) & _
> "%' OR description Like '%" & wordarray(1) & "%' etc.
>
> Did I mention that this will probably perform horribly?
>

Hmm, I wonder if a UNION query would perform better ... It can't hurt to
test it:

sSQL = "select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring2 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring2 & "%' " & _
"ORDER BY dateno DESC")

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
David
Guest
Posts: n/a
 
      02-08-2004

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob Barrows wrote:
>
> >
> > Anyways, you'll need to carry out your plan to break up the search
> > string into discrete words (you can use Split for this):
> > wordarray = split(searchstring, " ")
> >
> > and then loop through the array, adding an OR clause to the query for
> > each word in the array:
> > where articletext LIKE '%" & wordarray(0) & "%' OR description Like
> > '%" & _ wordarray(0) & "%' OR articletext LIKE '%" & wordarray(1) & _
> > "%' OR description Like '%" & wordarray(1) & "%' etc.
> >
> > Did I mention that this will probably perform horribly?
> >

> Hmm, I wonder if a UNION query would perform better ... It can't hurt to
> test it:
>
> sSQL = "select dateno, topic, title, ID, description from journals " & _
> "where articletext LIKE '%" & searchstring1 & "%' " & _
> "UNION select dateno, topic, title, ID, description from journals " & _
> "where description Like '%" & searchstring1 & "%' " & _
> "UNION select dateno, topic, title, ID, description from journals " & _
> "where articletext LIKE '%" & searchstring2 & "%' " & _
> "UNION select dateno, topic, title, ID, description from journals " & _
> "where description Like '%" & searchstring2 & "%' " & _
> "ORDER BY dateno DESC")


Thanks Bob - I'll try this.

David


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtle difference between boolean value and boolean comparison? Metre Meter Javascript 7 08-06-2010 08:40 PM
difference between 'boolean' and 'java.lang.Boolean' J Leonard Java 4 01-19-2008 02:56 AM
Boolean query search Andrej Hristoliubov Java 0 01-08-2006 04:55 PM
Boolean query search puzzlecracker Java 0 01-08-2006 03:34 PM
Boolean search query parsing (hierarchical) Robert Watkins Java 10 11-14-2003 01:59 PM



Advertisments