Case Insensitive Oracle Query

[saynords]

Hiya,

I am querying an Oracle database that I have no direct access to so cannot change server properties.
I need to do a case insensitive search on a name/or other variable (C# code)
i.e .....

"SELECT Field1, FIeld2 FROM TableName WHERE (Field1='" + Session["Variable"].ToString() + "'";

so that if the session variable was "Debbie"
it would bring back DEBBIE, debbie, DeBbIe etc.

Can anyone help?
thanks so much.
Debs

[r035198x]

Hiya,

I am querying an Oracle database that I have no direct access to so cannot change server properties.
I need to do a case insensitive search on a name/or other variable (C# code)
i.e .....

"SELECT Field1, FIeld2 FROM TableName WHERE (Field1='" + Session["Variable"].ToString() + "'";

so that if the session variable was "Debbie"
it would bring back DEBBIE, debbie, DeBbIe etc.

Can anyone help?
thanks so much.
Debs

You can UPPER both sides of the = sign to avoid case diffrences.

[saynords]

You can UPPER both sides of the = sign to avoid case diffrences.

could you give me an example because I 'm sure I tried that
thanks Debs

[saynords]

could you give me an example because I 'm sure I tried that
thanks Debs

The problem could be that I need to use it in conjunction with LIKE ;o(

i.e
SELECT Field1, FIeld2 FROM TableName WHERE (Field1 LIKE '" + Session["Variable"].ToString() + "'";
Is that possible?
Thanks
Debs

[r035198x]

The problem could be that I need to use it in conjunction with LIKE ;o(

i.e
SELECT Field1, FIeld2 FROM TableName WHERE (Field1 LIKE '" + Session["Variable"].ToString() + "'";
Is that possible?
Thanks
Debs

Yes it's possible

where UPPER(Field) LIKE UPPER(...)

P.S Sometimes the best way to learn things is to try them and observe the results.

[saynords]

Yes it's possible

where UPPER(Field) LIKE UPPER(...)

P.S Sometimes the best way to learn things is to try them and observe the results.

THanks yes that's what I've tried but its not working. I think the problem is something else. The variable I am passing in may contain invalid characters such as ';' or '/' - now I can of course use 'replace' to strip these out but as they exist in the oracle db field then the LIKE will not work. Is there a way round this?
thanks
Debs

[r035198x]

THanks yes that's what I've tried but its not working. I think the problem is something else. The variable I am passing in may contain invalid characters such as ';' or '/' - now I can of course use 'replace' to strip these out but as they exist in the oracle db field then the LIKE will not work. Is there a way round this?
thanks
Debs

Why do you want to replace those if you want to search for them? When you say it's not working do you mean it gives an error/exception?

[saynords]

Why do you want to replase those if you want to search for them? When you say it's not working do you mean it gives an error/exception?

yes sorry - ORACLE error 'invalid character'

[r035198x]

yes sorry - ORACLE error 'invalid character'

Post the code that you had used for this.

Edit: Why do you have the ( after the where?

[saynords]

Post the code that you had used for this.

Edit: Why do you have the ( after the where?

This is the code and I know the record exists. What am I doing wrong :O(

SQL=SELECT B.R_EXPEDIOREFERENCE ExpedioRef,B.R_CUSTOMERREFERENCE Reference,B.R_TITLE Title,B.R_CONTRACTREQUESTTYPE Type, B.R_REQUESTSTATUS Status, B.R_REQUESTTASK Task ,A.ORIGINATORSURNAME Originator, A.CONTACTSURNAME Requestor FROM aradmin.EXP__I_F__CREATE_REQUEST A, aradmin.exp__request B WHERE (B.R_SOURCE = 'KANA' AND A.EXPEDIOREFERENCE = B.R_EXPEDIOREFERENCE AND B.BFG_CONTRACTID='4318' AND (UPPER(R_TITLE) LIKE UPPER('%Retrospective: Meridian upgrade%')) AND A.ORIGINATORSURNAME='Saynor')

[r035198x]

This is the code and I know the record exists. What am I doing wrong :O(

SQL=SELECT B.R_EXPEDIOREFERENCE ExpedioRef,B.R_CUSTOMERREFERENCE Reference,B.R_TITLE Title,B.R_CONTRACTREQUESTTYPE Type, B.R_REQUESTSTATUS Status, B.R_REQUESTTASK Task ,A.ORIGINATORSURNAME Originator, A.CONTACTSURNAME Requestor FROM aradmin.EXP__I_F__CREATE_REQUEST A, aradmin.exp__request B WHERE (B.R_SOURCE = 'KANA' AND A.EXPEDIOREFERENCE = B.R_EXPEDIOREFERENCE AND B.BFG_CONTRACTID='4318' AND (UPPER(R_TITLE) LIKE UPPER('%Retrospective: Meridian upgrade%')) AND A.ORIGINATORSURNAME='Saynor')

Are you missing some AS words in there?
As in

SELECT B.R_EXPEDIOREFERENCE AS ExpedioRef,B.R_CUSTOMERREFERENCE AS Reference,B.R_TITLE AS Title,B.R_CONTRACTREQUESTTYPE AS Type ...

[amitpatel66]

This is the code and I know the record exists. What am I doing wrong :O(

SQL=SELECT B.R_EXPEDIOREFERENCE ExpedioRef,B.R_CUSTOMERREFERENCE Reference,B.R_TITLE Title,B.R_CONTRACTREQUESTTYPE Type, B.R_REQUESTSTATUS Status, B.R_REQUESTTASK Task ,A.ORIGINATORSURNAME Originator, A.CONTACTSURNAME Requestor FROM aradmin.EXP__I_F__CREATE_REQUEST A, aradmin.exp__request B WHERE (B.R_SOURCE = 'KANA' AND A.EXPEDIOREFERENCE = B.R_EXPEDIOREFERENCE AND B.BFG_CONTRACTID='4318' AND (UPPER(R_TITLE) LIKE UPPER('%Retrospective: Meridian upgrade%')) AND A.ORIGINATORSURNAME='Saynor')

Try below query:

SQL=SELECT B.R_EXPEDIOREFERENCE ExpedioRef,B.R_CUSTOMERREFERENCE Reference,B.R_TITLE Title,B.R_CONTRACTREQUESTTYPE Type, B.R_REQUESTSTATUS Status, B.R_REQUESTTASK Task ,A.ORIGINATORSURNAME Originator, A.CONTACTSURNAME Requestor FROM aradmin.EXP__I_F__CREATE_REQUEST A, aradmin.exp__request B WHERE B.R_SOURCE = 'KANA' AND A.EXPEDIOREFERENCE = B.R_EXPEDIOREFERENCE AND B.BFG_CONTRACTID='4318' AND UPPER(R_TITLE) LIKE UPPER('%Retrospective: Meridian upgrade%') AND A.ORIGINATORSURNAME='Saynor'
 

I have removed the "(" where ever not required