Home » SQL & PL/SQL » SQL & PL/SQL » how make IN operator not case sensitive ?
icon4.gif  how make IN operator not case sensitive ? [message #303743] Sun, 02 March 2008 11:23 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

create table Payment_Methods (
Payment_Method_ID number primary key,
Payment_Method varchar2(11)
check (Payment_Method in ( 'Cash','Check','Credit Card' ) ));


the problem here .... if I insert in the Payment_Method column
by lower case or by upper case , the error will appear like this :-

SQL> insert into Payment_Methods values (
  2  1,'cash');
insert into Payment_Methods values (
*
ERROR at line 1:
ORA-02290: check constraint (DE.SYS_C0010420) violated


and if upper case :-

SQL> ed
Wrote file afiedt.buf

  1  insert into Payment_Methods values (
  2* 1,'CASH')
SQL> /
insert into Payment_Methods values (
*
ERROR at line 1:
ORA-02290: check constraint (DE.SYS_C0010420) violated


only initcap case working (like I wrote in create table statement )

SQL> ed
Wrote file afiedt.buf

  1  insert into Payment_Methods values (
  2* 1,'Cash')
SQL> /

1 row created.


How can I make the Payment_Method column NOT case sensitive Question Question Exclamation Exclamation
Re: how make IN operator not case sensitive ? [message #303744 is a reply to message #303743] Sun, 02 March 2008 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lower(col) in (values in lower case)

Regards
Michel
icon7.gif  Re: how make IN operator not case sensitive ? [message #303768 is a reply to message #303744] Sun, 02 March 2008 13:10 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

WHICH MEANS I HAVEN'T NOWAY BUT THAT WAY ONLY :-

SQL> INSERT INTO PAYMENT_METHODS VALUES (
  2  4,INITCAP('CASH'));

1 row created.


OKAY Confused
Re: how make IN operator not case sensitive ? [message #303769 is a reply to message #303743] Sun, 02 March 2008 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
THINK!


If you are a glutton for punishment, you could do as follows:
create table Payment_Methods (
Payment_Method_ID number primary key,
Payment_Method varchar2(11)
check (Payment_Method in ( 'Cash','CASH','cash','Check','Credit Card' ) ));

BUT it is much, much, much better to only use a single value.

[Updated on: Sun, 02 March 2008 13:25] by Moderator

Report message to a moderator

icon10.gif  Re: how make IN operator not case sensitive ? [message #303777 is a reply to message #303769] Sun, 02 March 2008 16:36 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Laughing Okay Tiger ..... I THINK may be you mean something like that :-

create table Payment_Methods (
Payment_Method_ID number primary key,
Payment_Method varchar2(11)
check (initcap(Payment_Method) in ( 'Cash','Check','Credit Card' ) ));



SQL> insert into Payment_Methods values (1,'cash');

1 row created.

  1* insert into Payment_Methods values (2,'CASH')
SQL> /

1 row created.

SQL> select*from Payment_Methods;

PAYMENT_METHOD_ID PAYMENT_MET
----------------- -----------
                1 cash
                2 CASH

SQL> SELECT INITCAP(Payment_Method) FROM Payment_Methods;

INITCAP(PAY
-----------
Cash
Cash


COOL Question Laughing Thumbs Up
Re: how make IN operator not case sensitive ? [message #303780 is a reply to message #303743] Sun, 02 March 2008 17:31 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
alternatively it might be better to store the values in a consistent manner so you know what to expect upon SELECT.
insert into Payment_Methods values (1,initcap('cash'));
insert into Payment_Methods values (2,initcap('CASH'));
Previous Topic: how to update a table using data from a different table
Next Topic: How to use a schema name parameter in a procedure
Goto Forum:
  


Current Time: Sat Dec 03 03:42:12 CST 2016

Total time taken to generate the page: 0.08754 seconds