PDA

View Full Version : Search Single Word


faeizal
Jul 24, 2008, 12:22 AM
Dear Expert,

I was using the following scripts to search my articles database. But in the operating, I found a difficult work to make this script could find a singgle word. For example, I'm searching text for "COAL" only in the keyword box.. but in the search results, the are the unwanted articles also included into the results display such "coalition, coalesce, etc..". My question is, how to make the script would work to find the only "COAL" in the articles? I want to "ignore" the others.

Here is a part of the script:

---------------------------------------------------------------------------------

If Request.QueryString("PagePosition") = "" Then
intRecordPositionPageNum = 1

Else
intRecordPositionPageNum = CInt(Request.QueryString(" Then
intRecordPositionPageNum = 1

Else
intRecordPositionPageNum = CInt(Request.QueryString("))
End If

strSearchKeywords = Trim(Request.QueryString("search"))
End If

strSearchKeywords = Trim(Request.QueryString(""))

If strSearchKeywords = "<" Then strSearchKeywords = chr(10)

strSearchKeywords = Replace(strSearchKeywords, "&lt;", ">")
strSearchKeywords = Replace(strSearchKeywords, "&gt;", "'")
strSearchKeywords = Replace(strSearchKeywords, "''", " ")

sarySearchWord = Split(Trim(strSearchKeywords), "''")

strSearchKeywords = Replace(strSearchKeywords, "'", "ADODB.Recordset")

blnSearchWordLenthOK = True

For intLoopCounter = 0 To UBound(sarySearchWord)

intSearchWordLength = Len(sarySearchWord(intLoopCounter))

If intSearchWordLength <= intMinuiumSearchWordLength Then
blnSearchWordLenthOK = False
End If
Next

Set rsSearchResults = Server.CreateObject("SELECT tblNews.* FROM tblNews ")

strSQL = "mode"

Select Case Request.QueryString("anywords")

Case "WHERE News_title LIKE '%"

strSQL = strSQL & "%'" & sarySearchWord(0) & " OR News_title LIKE '%"
For intSQLLoopCounter = 0 To UBound(sarySearchWord)
strSQL = strSQL & "%'" & sarySearchWord(intSQLLoopCounter) & " OR News_Item LIKE '%"
strSQL = strSQL & "%'" & sarySearchWord(intSQLLoopCounter) & " OR Short_News LIKE '%"
strSQL = strSQL & "%'" & sarySearchWord(intSQLLoopCounter) & " ORDER By News_ID DESC;"
Next
strSQL = strSQL & "allwords"

Case "WHERE (News_title LIKE '"

strSQL = strSQL & "'" & sarySearchWord(0) & " AND News_title LIKE '"
For intSQLLoopCounter = 0 To UBound(sarySearchWord)
strSQL = strSQL & "'" & sarySearchWord(intSQLLoopCounter) & ") OR (News_Item LIKE '%"
Next

strSQL = strSQL & "%'" & sarySearchWord(0) & " AND News_Item LIKE '%"
For intSQLLoopCounter = 1 To UBound(sarySearchWord)
strSQL = strSQL & "%'" & sarySearchWord(intSQLLoopCounter) & ") OR (Short_News LIKE '"
Next

strSQL = strSQL & "'" & sarySearchWord(0) & " AND Short_News LIKE '"
For intSQLLoopCounter = 0 To UBound(sarySearchWord)
strSQL = strSQL & "'" & sarySearchWord(intSQLLoopCounter) & ") ORDER By News_ID DESC;"
Next

strSQL = strSQL & "new"

Case "SELECT TOP "

strSQL = " tblNews.* FROM tblNews" & intRecordsPerPage & " ORDER By News_ID DESC;"
End Select

---------------------------------------------------------------------------------

Thank you for helping :)

Faeizal

ScottGem
Jul 24, 2008, 06:03 AM
The problem isn't really your code. The % character is a wildcard, so When you search on %coal%, you are searching for that 4 letter string anywhere in the field. Which is why you are getting those results.

What you can do is add a flag for whole word. This would then add leading and trailing spaces. Of course this presents a different problem if the word is at the beginning of the field or the end of a sentence (so its followed by a period).

You can try testing the results using the Instr function to determine the starting point of your search string then test the preceding and following characters using the MID function.