SQL exact search

colinod

I have the following code that works very well, the problem is that of i search for say rap it returns back results that contain the words telegraph, grapfruit etc, how can i get it to just return results that have the word rap in the search string anywhere??

Expand|Select|Wrap|Line Numbers

 <%

Dim strSearch, myarray, strSQL, strSQLExtra 

If Not Request.Form("search") = "" Then 

session("search") = Request.Form("search") 

session("choice") = Request.Form("choice") 

session("queryterm") = session("queryterm")+" "+Request.Form("search")

End If 
 
'Loop is now in a function, so that we can feed it each field name. 

Function addparams(fieldname,wordArray)  

Dim tmp 

tmp = "" 

    For each item in wordArray 

    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & session("choice") & " " 

    next 

    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 

    addparams = tmp 

End Function 
 
'Build the word array 

myarray = split(session("search"), " ") 
 
'Build the SQL  

strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
 
strSQL = strSQL & addparams("filename", myarray) ' change field one! 
 
strSQL = strSQL & ") OR (" 
 
strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
 
strSQL = strSQL & ")order by surname, firstname, filename;" 

session("sqlset") = strSQL
 
  %>

CroCrew

Hello colinod,

Here is an example that does what your looking to do.

Expand|Select|Wrap|Line Numbers

 
<%

    Function WordFinder(FullString, WordToFind)   

        myarray = split(FullString)

        For each item in myarray  

            If (item = WordToFind) Then

                WordFinder = "Found!"

                Exit Function

            End If

        next  

        WordFinder = "Not Found."

    End Function  

%>

<html>

    <head>

        <title>Test Page</title>

    </head>

    <body>

        <%

            If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then

                Response.Write("Looking for [" & Request.Form("xFind") & "]<br />")

                Response.Write("In [" & Request.Form("xString") & "]<br />")

                Response.Write(WordFinder(Request.Form("xString"), Request.Form("xFind")))

            End If

        %>
 
        <form method="POST" name="xForm" action="Index.asp">

            String: <input type="text" name="xString"><br />

            Find this: <input type="text" name="xFind"><br />

            <input type="Submit" name="xSubmit" value="Find">

        </form>

    </body>

</html>

Hope it helps,
CroCrew~

CroCrew

in the first box type in "Hello how are you today Michelle" and the second box type in "to". There should be no matches.

then for the second test:

in the first box type in "Hello how are you today Michelle" and the second box type in "today". There should be a match now.

Hope that that helped you out,
CroCrew~

colinod

Hi thanks for that but i am trying to do this with sql in an asp page, am i in the wrong place for this question?

CroCrew

Hello Colinod,

I think you'd have to search for '% rap %' and include the spaces in the search. Like this:

Expand|Select|Wrap|Line Numbers

 
SELECT * FROM [table] WHERE (([field] LIKE '% rap %') OR ([field] LIKE '% rap%') OR ([field] LIKE '%rap %'))

Hope that helps,
CroCrew~

SQL exact search

re: SQL exact search

re: SQL exact search

re: SQL exact search

re: SQL exact search

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about