Building a SQL Search Query--Easily

Submitted on: 1/5/2015 3:41:00 PM
By: Primoris Software (from psc cd)  
Level: Intermediate
User Rating: By 3 Users
Compatibility: ASP (Active Server Pages)
Views: 3831
     This article explains an alternative to the boring string parse/loop method of building a search query for your website.


Building a Better Query

What kind of Query does this apply to?


This 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.

So 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.

After 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.


The Alternative Way to Build the Query


The 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 like,


while (" ") != -1)


strObj = strObj.replace(" "," ");


return strObj.toString()


In 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.

Now 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.

Either 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 )

Dim selectClause

Dim fromClause

Dim whereClause

selectClause = "SELECT * "

fromClause = "FROM table "

whereClause = "WHERE "

Select Case Request.QueryString("optAll")

Case 1 ' Match ALL keywords

whereClause = whereClause & " (field LIKE '%" & _

Replace( Trim( strText ), " ", "%' AND field LIKE '%") & "%')"

Case 2 ' Match ANY keywords

whereClause = whereClause & " (field LIKE '%" & _

Replace( Trim( strText ), " ", "%' OR field LIKE '%") & "%')"

End Select

'Response.Write( selectClause & fromClause & whereClause )

buildSQL = selectClause & fromClause & whereClause

End Function


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.



Other 5 submission(s) by this author


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

 There are no comments on this submission.

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.