Complex Keyword Searches

John Avis by | August 7, 2007 | Classic ASP

This article is about how you can generate complex SQL queries for searching database columns for keywords.

The examples assume that your keywords are in a variable called strKeywords.

First we need to remove multiple spaces.

Do While InStr(strKeywords, "  ") > 0
strKeywords = Replace(strKeywords, " ", " ")
Loop
The next step is to filter out any invalid characters. Only letters and numbers, spaces, quotes and hyphens are accepted.

strKeywords = LCase(strKeywords)
For lngChar = 1 To Len(strKeywords)
If Not (Asc(Mid(strKeywords, lngChar, 1)) = 34 Or _
Asc(Mid(strKeywords, lngChar, 1)) = 45 Or _
(Asc(Mid(strKeywords, lngChar, 1)) >= 48 And _
Asc(Mid(strKeywords, lngChar, 1)) <= 57) Or _
(Asc(Mid(strKeywords, lngChar, 1)) >= 97 And _
Asc(Mid(strKeywords, lngChar, 1)) <= 122) Or _
Asc(Mid(strKeywords, lngChar, 1)) = 32 Or _
Asc(Mid(strKeywords, lngChar, 1)) = 46) Then
strKeywords = Left(strKeywords, lngChar - 1) & _
" " & Mid(strKeywords, lngChar + 1)
End If
Next
This next procedure is optional and can be used to lookup phrases in a user dictionary and change them. For example, if you wanted searches for the word "Landrover" to also find the words "Land" and "Rover" you could do this with this procedure. A database table is required for the dictionary called "Dictionary", with the columns (text) "Lookup" and "Replace" which contain the phrase to find and the phrase to replace with.

strKeywords = " " & strKeywords & " "
Set rsDictionary = Server.CreateObject("ADODB.Recordset")
rsDictionary.Open "SELECT * FROM Dictionary", database object
Do Until rsDictionary.Eof
strLookup = " " & rsDictionary("Lookup") & " "
If InStr(strKeywords, strLookup) > 0 Then
strReplace = rsDictionary("Replace")
strReplace = " " & strReplace & " "
strKeywords = Replace(strKeywords, strLookup, _
strReplace)
End If
rsDictionary.MoveNext
Loop
rsDictionary.Close
Set rsDictionary = Nothing
strKeywords = Trim(Mid(strKeywords, 2))
This next procedure makes sure that phrases that are surrounded by quotation marks are treated as one and must match exactly.

intStart = InStr(strKeywords, """")
Do While intStart > 0
If intStart = Len(strKeywords) Then Exit Do
intEnd = InStr(intStart + 1, strKeywords, """")
If intEnd = 0 Then Exit Do
For intCount = intStart To intEnd
If Mid(strKeywords, intCount, 1) = " " Then
strKeywords = Left(strKeywords, intCount - 1) & _
"+" & Mid(strKeywords, intCount + 1)
End If
Next
If intEnd = Len(strKeywords) Then Exit Do
intStart = InStr(intEnd + 1, strKeywords, """")
Loop
strKeywords = Replace(strKeywords, """", "")
Now that all the manipulation of the keywords has been done we can create an array containing all the keywords of phrases if surrounded by quotation marks.

intWords = 0
intStart = 1
intEnd = 1
ReDim strWords(0)
Do While Len(strKeywords) >= intEnd
If intStart > 1 Then intStart = InStr(intEnd, strKeywords, " ", 0)
If intStart = 0 Then Exit Do
intEnd = InStr(intStart + 1, strKeywords, " ", 0)
If intEnd = 0 Then intEnd = Len(strKeywords) + 1
intWords = intWords + 1
redim preserve strWords(intWords)
strWords(intWords) = Replace(LTrim(RTrim(Mid(strKeywords + " ", _
intStart, intEnd - intStart))), "+", " ")
If intStart = 1 Then intStart = 2
Loop
Finally we can generate a SQL query to search for the keywords of phrases in one or more database columns.

strQuery = ""
If intWords <> 0 Then
If strQuery <> "" Then strQuery = strQuery & " AND"
strQuery = strQuery & " ("
For intCounter = 1 To intWords
If you wish to be able to do searches based on finding ALL of the keywords or ANY of the keywords set the variable strAndOr accordingly on the following line (to AND for all, or OR for any).

		If intCounter > 1 And strQuery <> "" Then strQuery = _
strQuery & " " & strAndOr
The next line will vary depending on how many columns you want to search in. For one column it would be as follows:

		strQuery = strQuery & " (fieldname LIKE '%" & _
strWords(intCounter) & "%')"
But for multiple columns it would be:

		strQuery = strQuery & " (fieldname1 LIKE '%" & _
strWords(intCounter) & "%' OR fieldname2 LIKE '%" & _
strWords(intCounter) & "%' OR fieldname3 LIKE '%" & _
strWords(intCounter) & "%')"
Then we finish off our query.

	Next
strQuery = strQuery & ")"
End If
strQuery = "SELECT * FROM tablename"
If strQuery <> "" Then strQuery = strQuery & " WHERE" & strQuery
Obviously you can add more search criteria and ORDER BY clauses as neccessary.

Related Posts

Classic ASP

Sending email using Amazon SES with Classic ASP

by John Avis | March 21, 2017

I recently needed to change a client's website to send emails using Amazon SES and encountered a few issues.


Classic ASP

Classic ASP class constructors with parameters

by John Avis | June 8, 2016

Although probably no one cares about Classic ASP, except those who still need to support it, I was refreshing my memory today on how to use classes in Classic ASP and found a couple of options for simulating constructors with parameters.


Classic ASP

Classic ASP cookie with no name hacking attack causing error 80004005

by John Avis | July 31, 2015

As of July 2015, many of my Classic ASP websites have started logging strange errors, either 80004005 or not reporting any error code at all.

Comments

EllieZ

by EllieZ | February 7, 2010

Dude - You Rock! This script is EXACTLY what I needed - THX :-)

Reply

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP Youtube

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.