Building a Better Query
What kind of Query
does this apply to?
tutorial is meant to cover the type of queries where someone is running a
single or multiple keyword search on your web site (or, I suspect this could be
used in software development as well, though I haven't used it yet).
For example, you have a knowledge base, and a customer is
looking up information on a printing bug.
So they go to your "Search" field and type "print bug
epson". Sound easy? You're not saying, 'Oh, no problem, the query
would look like SELECT * FROM TABLE WHERE field LIKE '%" & textbox
& "%'"', are you? The
problem is, people many times type their keywords in an order different than
that found in your knowledge base, and certainly there's the possibility of
other words between the keywords.
how do you overcome this? One solution
(widely used) is to use a loop. Parse
all of the words with a commonly used parsing function. Sure, you can download one from one of the
free code places on the web, but then you have to find it and figure out how to
use it. You can write it yourself, but
I promise you it is one of the most boring pieces of code you will ever write.
all of the keywords are parsed into an array, build your query by looping
through all of them. Fast? Easy?
Efficient? Kind of. But here's a better, more organized way.
Way to Build the Query
alternative comes in two parts. Step
one is to remove all extraneous white space from the string. It probably
doesn't have any, but you never know.
So, do this on the client side with a function that loops something
(strObj.search(" ") != -1)
= strObj.replace(" ","
case you didn't catch it, in the while condition there is a string with two
spaces, and in the replace function, the string with two spaces is replaced
with one. Until there is only one space
between (or in front or behind of) every keyword, keep stripping it down.
you have a nicely organized string of keywords with one space max between,
before, or after the words. On the
search page, you may also have conditions like "case sensitive",
matches per page, etc, but we won't go over that here (if you want me to cover
that at some point, simply send me a note and I'll try to do it).
Pass this string to your next active server page. For me, did you notice that I returned the
string from a function in the code above?
I stuck that return value back in the original text box. It's up to you to decide how you want to
pass the string back.
way, now the ASP has to build the search string. Here's the good part—step two.
Now that your string has only single spaces, you can strip the first and
last spaces, and run a replace on all of the remaining. Let's build this step by step (the way I do
on my page).
Function buildSQL( strText )
= "SELECT * "
= "FROM table "
= "WHERE "
1 ' Match ALL keywords
whereClause = whereClause & "
(field LIKE '%" & _
Replace( Trim( strText ), "
", "%' AND field LIKE '%") & "%')"
2 ' Match ANY keywords
= whereClause & " (field LIKE '%" & _
Replace( Trim( strText ), "
", "%' OR field LIKE '%") & "%')"
selectClause & fromClause & whereClause )
= selectClause & fromClause & whereClause
So you're replacing all of the
middle spaces with a SQL 'and' statement.
In plain English, if your search phrase is "print bug", this
now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate
the leading and trailing %'s and quotes (this is for Microsoft Access drivers,
other drivers may use different wildcards)--so just append this phrase to the
"WHERE field LIKE " phrase, and you're in business. I've built gigantic search phrases with this
method before with little coding, and little server load.
Voila! An instant search query! No tiresome string parsing or looping. One final question you may have is,
"what if the user separates the keywords with commas or hyphens or...". No problem!
Just put client-side code in to convert all hyphens, commas, etc. to
white space. Put this before the function that strips the
white-space down to one. String:
Normalized. So that's how it's
done. If you have any questions, I
would be happy to explain further--just send me a note on Planet Source Code.