Building a SQL Search Query--Easily
This article explains an alternative to the boring string parse/loop method of building a search query for your website.
Original Author: Primoris Software
Code
This For example, you have a knowledge base, and a customer is So After The In Now Pass this string to your next active server page. Either Voila! 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).
looking up information on a printing bug.?á
So they go to your "Search" field and type "print bug
epson".?á Sound easy?
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.
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?
Efficient??á Kind of.The Alternative
Way to Build the Query
alternative comes in two parts.?á Step
one is to remove all extraneous white space from the string.
doesn't have any, but you never know.?á
So, do this on the client side with a function that loops something
like,
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á while
(strObj.search("?á ") != -1)
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á {
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á strObj
= strObj.replace("?á ","
");
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á }
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á return
strObj.toString()
mso-bidi-font-family:"Times New Roman"'>
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).
string from a function in the code above??á
I stuck that return value back in the original text box.
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.
on my page).
mso-bidi-font-family:"Times New Roman"'>Function buildSQL( strText )
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á Dim
selectClause
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á Dim
fromClause
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á Dim
whereClause
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á selectClause
= "SELECT?á * "
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á fromClause
= "FROM table "
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á whereClause
= "WHERE "
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á Select
Case Request.QueryString("optAll")
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á Case
1 ' Match ALL keywords
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á
(field LIKE '%" & _
mso-bidi-font-family:"Times New Roman"'>Replace( Trim( strText ), "
", "%' AND field LIKE '%") & "%')"
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á
2 ' Match ANY keywords
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á?á whereClause
= whereClause & " (field LIKE '%" & _
mso-bidi-font-family:"Times New Roman"'>Replace( Trim( strText ), "
", "%' OR field LIKE '%") & "%')"
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á End
Select
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á 'Response.Write(
selectClause & fromClause & whereClause )
mso-bidi-font-family:"Times New Roman"'>?á?á?á?á?á?á?á?á?á buildSQL
= selectClause & fromClause & whereClause
mso-bidi-font-family:"Times New Roman"'>End Function
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.
method before with little coding, and little server load.
"what if the user separates the keywords with commas or hyphens or...".
Just put client-side code in to convert all hyphens, commas, etc. to
white space.?á Put this
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.
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.