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("PagePosition"))
End If

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

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

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

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

strSearchKeywords = Replace(strSearchKeywords, "''", "'")

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("ADODB.Recordset")

strSQL = "SELECT tblNews.* FROM tblNews "

Select Case Request.QueryString("mode")

Case "anywords"

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

Case "allwords"

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

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

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

strSQL = strSQL & ") ORDER By News_ID DESC;"

Case "new"

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

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

Thank you for helping

Faeizal

ScottGem

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 preceeding and following characters using the MID function.