CODEWITHSUNDEEP
oracle11g
sarah khaled
sarah khaled

Reputation: 91

Ignore case in Oracle 11g

Is there any way to set table or fields to be ignore case? And I don't want to use lower or upper function. I'm shocked from oracle if they not support Ignore case, I still searching but I didn't find any solution

Upvotes: 0

Views: 203

Answers (1)

Francisco Sitja
Francisco Sitja

Reputation: 1003

As you said, you can't explicitly make a column "case insensitive".

You can, however, work around it by using virtual columns, triggers, check constraints or just querying using case insensitive predicates and upper/lower functions.

Some examples:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as fsitja@sitja

SQL> 
SQL> create table job (JOB_TITLE varchar2(40));
Table created
SQL> alter table job add constraint ck_job_upper check (job_title = upper(job_title));
Table altered
SQL> insert into job values ('clerk');
insert into job values ('clerk')
ORA-02290: restrição de verificação (FSITJA.CK_JOB_UPPER) violada
SQL> insert into job values ('CLERK');
1 row inserted
SQL> commit;
Commit complete
SQL> create or replace trigger tr_br_upper_job_title before insert or update of job_title on job
  2  for each row
  3  begin
  4    :new.job_title := upper(:old.job_title);
  5  end;
  6  /
Trigger created
SQL> insert into job values ('clerk');
1 row inserted
SQL> select * from job;
JOB_TITLE
----------------------------------------
CLERK

SQL> commit;
Commit complete

SQL> 
SQL> drop table job;
Table dropped
SQL> create table job (JOB_TITLE varchar2(40), upper_job_t as (upper(job_title)));
Table created
SQL> insert into job (job_title) values ('CLERK');
1 row inserted
SQL> insert into job (job_title) values ('clerk');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from job where regexp_like(job_title, 'CLERK', 'i');
JOB_TITLE                                UPPER_JOB_T
---------------------------------------- ----------------------------------------
CLERK                                    CLERK
clerk                                    CLERK

SQL>

Upvotes: 1

Related Questions