2. ASPRunnerPro General questions
  3. [SOLVED]  Ignore case when searching
This topic is locked
[SOLVED]

 Ignore case when searching

 		10/5/2003 9:49:43 PM
ASPRunnerPro General questions
N
 noldfield author

I have setup a staff lookup facility using asprunner querying a text file (exported from Active Directory). I would like the searches to be case in-sensitive so that when I search for "he" it will find both Rhett and Heterich.
Thanks

Nathan
S
 		Sushy99 10/16/2003

Hi,
i would like to know the solution too!
Nick
Sergey Kornilov admin 10/19/2003

Hi,
here is how it can be done (sorry about delay with answer).
In ..._list.asp file find a section that builds search SQL query.

if Request.Form("action")="Search" and Request.Form("SearchFor")<>""  then

strSearchFor=Trim(Request.Form("SearchFor"))

strSearchOption=Trim(Request.Form("SearchOption"))
if Request.Form("SearchField")<>"AnyField" then

    strSQL = strSQL & " where " & AddWrappers(Request.Form("SearchField")) & StrWhere(Request.Form("SearchField"), strSearchFor, strSearchOption)

else

strSQL = strSQL & " where 1=0"

 

strSQL = strSQL & " or " & AddWrappers("AREACODE") & StrWhere("AREACODE", strSearchFor, strSearchOption)

 

strSQL = strSQL & " or " & AddWrappers("STATE") & StrWhere("STATE", strSearchFor, strSearchOption)

 

strSQL = strSQL & " or " & AddWrappers("MAJORCITIES") & StrWhere("MAJORCITIES", strSearchFor, strSearchOption)

 

    end if

end if


Make changes in bold for all required fields. I hope this helps.

if Request.Form("action")="Search" and Request.Form("SearchFor")<>""  then

strSearchFor=UCase(Trim(Request.Form("SearchFor")))

strSearchOption=Trim(Request.Form("SearchOption"))
if Request.Form("SearchField")<>"AnyField" then

    strSQL = strSQL & " where UCase(" & AddWrappers(Request.Form("SearchField")) & ") " & StrWhere(Request.Form("SearchField"), strSearchFor, strSearchOption)

else

strSQL = strSQL & " where 1=0"

 

strSQL = strSQL & " or UCase(" & AddWrappers("AREACODE") & ") " & StrWhere("AREACODE", strSearchFor, strSearchOption)
strSQL = strSQL & " or UCase(" & AddWrappers("STATE") & ") " & StrWhere("STATE", strSearchFor, strSearchOption)

 

strSQL = strSQL & " or UCase(" & AddWrappers("MAJORCITIES") & ") " & StrWhere("MAJORCITIES", strSearchFor, strSearchOption)

 

    end if

end if
D
 		ducmis 1/11/2005

what if you are using MS SQL and it doesn't recognize UCase, what function should i use.?
Sergey Kornilov admin 1/13/2005

Hi,
in MS SQL you can use UPPER function.
UPPER

Returns a character expression with lowercase character data converted to uppercase.
Syntax

UPPER ( character_expression )
Arguments

character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
Return Types

varchar