Loading ...
Custom Column Keyword Searching
So the following code allows you to generate a custom SQL statement for your keyword searching.
You can specify any SELECT statement, FROM any tables or JOINS, specify WHICH columns you want to MATCH against, then compare ALL WORDS, ANY WORD or EXACT PHRASES against ANY NUMBER OF KEYWORDS and finally choose how you want your results ORDERED.
I’ve implemented as separate Visual Basic .Net class which can then be called from anywhere in your application. I originally used this in standard ASP and it wouldn’t take much to adapt it back.
Download: Custom Column SQL Searching - Downloaded 67 Times
Public Sub ConstructSQL()
Dim countColumn, i, j, searchField
'Make an array of the search columns
Dim searchColumnArray
Dim searchArray
searchColumnArray = Split(searchColumns, ",")
countColumn = UBound(searchColumnArray)
' Start building SQL string
' Add select statement to beginning of searchString
_sql = selectString + fromString
If searchString <> "" Then
' If there are keywords then continue to build SQL
_sql += " WHERE ("
' Make an array of the keywords
searchField = searchString
searchField = Replace(searchField, "'", "")
searchArray = Split(searchField, " ")
'Checks the Comparison Type, Executes this code if the option All Words or Any Word is chosen
If (comparisonType <> "Exact Phrase") Then
Select Case comparisonType
Case "All Words"
comparisonType = "AND"
Case "Any Word"
comparisonType = "OR"
End Select
'Start loop for each column
For j = 0 To UBound(searchColumnArray)
'Start loop for each keyword
For i = 0 To UBound(searchArray)
'if it is the first keyword then open bracket for first SQL segment, i.e. (column1 LIKE keyword1....
If i = LBound(searchArray) Then
_sql += "("
End If
'add the SQL segments for each column per keyword
_sql += " " & searchColumnArray(j) & " LIKE '%" & searchArray(i) & "%' "
'if not the last keyword then put AND/OR in SQL, i.e. (column1 LIKE keyword1 AND....
If i < UBound(searchArray) Then
_sql += comparisonType & " "
Else
'if it is the last keyword and the last column then close SQL, i.e. .... column3 LIKE keyword3)
If (i = UBound(searchArray)) And (j = UBound(searchColumnArray)) Then
_sql += ")"
Else
'if it is the last keyword but not the last column put in OR, i.e. .... keyword2) OR (column3 LIKE keyword3....
_sql += ") OR "
End If
End If
'next keyword
Next
'next column
Next
'if Comparison Type is EXACT
Else
'Start loop for each column
For j = 0 To UBound(searchColumnArray)
_sql += " " & searchColumnArray(j) & " LIKE '%" & searchField & "%' "
If j < UBound(searchColumnArray) Then
_sql += " OR "
Else
_sql = _sql
End If
Next
End If
_sql += ")"
End If
_sql += " ORDER BY " & orderBy & " " & orderType
End Sub
Downloaded a total of 67 times
Leave a Reply