in oracle how can i eliminate case sensitivity while searching a string

Question

3. What if the role mentioned in table is ‘MANAGER’ or ‘Manager’? You can not write three different queries to get the result. What is the option to check all the possible values of role manager with the designation?

--selecting employee who is not a  manager 
create table employee(
employee_id number(10) NOT NULL,
employee_name varchar2(50),
employee_sal number(10),
employee_role varchar2(50),
employee_age number(10),
CONSTRAINT employee_pk PRIMARY KEY(employee_id)
);

insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(12,'ravi',5676,'dse',23);
insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(13,'sai',85676,'tester',21);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(15,'chandu',4676,'developer',28);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(16,'raju',7676,'tech lead',22);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(17,'teja',9676,'manager',29);
   
  select * from employee
  where employee_role !='manager';

user_5040684 · Answer

The quick and dirty way would be to compare with the column converted to uppercase or lowercase:

SELECT * FROM employee WHERE UPPER(employee_role) != 'MANAGER';

A more correct way depending on you language and use would be to add COLLATE operators to the search. For more detailed information about the COLLATE operator you can begin with this article: https://blogs.oracle.com/sql/post/how-to-do-case-insensitive-and-accent-insensitive-search-in-oracle-database

user_230471 · Answer

From 12.2 onwards:

select * from employee
where  employee_role collate binary_ci != 'Manager';

binary_ai is additionally accent-insensitive.

See collate documentation.

In theory you can specify collate binary_ci as part of the column definition when creating or altering the table (or when creating a view) to make it case-insensitive by default, but as this requires MAX_STRING_SIZE to be set to 'EXTENDED' at the database level, and this is a one-way operation that requires thorough compatibility testing, I can't see many sites taking the risk just to make case-insensitive searching more convenient.

in oracle how can i eliminate case sensitivity while searching a string

2 Answers