oracle fuzzy inquery, using ignore case.

Here is an example:

 

Prepare data:

create table fruit

(

    id number(4) primary key,

    name varchar2(150)

); 

 

Insert into fruit(ID,NAME) values (1,'Apple');
Insert into fruit (ID,NAME) values (2,'Banana');
Insert into fruit(ID,NAME) values (3,'Pear');
Insert into fruit(ID,NAME) values (4,null);

 

commit;

 

select  id, name from  fruit;

 

Here is the output:

 

ID                       NAME

1                         Apple

2                         Banana

3                         Pear

4                         null

 

select  id, name from fruit where  lower(name) like '%p%';      ('%p%', p isminuscule here.)

OR

select  id, name from fruit where  upper(name) like '%P%';     ('%P%', P is capitalized here.)

 

Here is the output:

 

ID                       NAME

1                         Apple

3                         Pear

 

From the result, you can see if the value of name column containscharacter 'p', no matter 'p' is minuscule or capitalized ,

that record will be listed in the query result.