Complex Keyword Searches

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.