case sensitivity & search form

[shervell]

I've created an intranet search html/asp form querying an oracle database & now my users want a non case sensitive search....

might it be possible to change the search criteria? i know the data is entered onto oracle via a dos-based front end that I have no control over & that it permits upper or lower case. i guess if I can't design a search with non case sensitivity searching by upper & lower case would be good... any ideas? I guess I'd have to take the string entered into the html form & use javascript to convert it to upper & also to lower & then pass the strings as search criteria & dispaly the results within an ASP page...

Any pointers would be great!!!

[QuackHead]

Take your search text (the stuff you're searching through) and convert it to lower case...

then convert the search words that the users enter into lower case...

<%
SearchText = LCase(SearchText)
SearchWords = LCase(SearchWords)
%>

Will that help?

[shervell]

Hmmmm????

parameter is passed from form & then query is made using sql:


FROM XAL_SUPERVISOR.DEBTABLE
WHERE ACCOUNTNUMBER like '%MMColParam%' AND DATASET = 'SPD' OR ACCOUNTNUMBER like '%MMColParam%' AND DATASET = 'REF' OR ACCOUNTNUMBER like '%MMColParam%' AND DATASET = 'HAW' OR ACCOUNTNUMBER like '%MMColParam%' AND DATASET = 'MIC'
ORDER BY ACCOUNTNUMBER ASC

as its oracle on unix its case senstive if it was sqlserver it wouldn't be an issue
: (
any ideas? how would I apply your suggestion to the field value/parameter?

cheers

[shervell]

hey quackhead whatabout..

hey quackhead whatabout changing the sql & including a

"select UPPER () FROM ....)
so:
WHERE ACCOUNTNUMBER like '%MMColParam%' AND DATASET = 'SPD'
would be:
WHERE ACCOUNTNUMBER like UPPER('%MMColParam%') AND DATASET = 'SPD'

This would convert everything to upper & could do the same for lower & display...


Graham