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

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';

CodePudding user response：

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

CodePudding user response：

From 12.2 onwards:

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

binary_ai is additionally accent-insensitive.