Complex Keyword Searches

John Avis by | August 7, 2007 | Classic ASP Web Development

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

Computers & Internet Web Development Website Hosting

500 Internal Server Error after migrating from IIS 7.5 to IIS 10

by John Avis | November 4, 2019

As support ends for Microsoft Windows Server 2008 I have recently gone through migrating some websites to a new server running Windows Server 2016 and IIS 10 but some of the websites did not work.


Jquery/Javascript Web Development

tagInput: A simple jQuery plugin for tag entry using Bootstrap 4

by John Avis | October 15, 2019

For a website project I needed a way to enter multiple tags. I just wanted something simple that I could easily modify to suit my own needs, so I wrote my own.


ASP.NET Web Forms Web Development

ASP.NET bug with RadioButton GroupName in Repeater

by John Avis | September 1, 2019

I rediscovered a bug in ASP.NET that affects RadioButtons inside repeaters. Here is my solution to the problem.

Comments

EllieZ

by EllieZ | February 7, 2010

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

Reply

Leave a Comment

Tags

About me

...random postings about web development and programming, Internet, computers, electronics and automotive topics.

Subscribe

Get the latest posts delivered to your inbox.