SQL & PL/SQL

H,

Can you please help me finding a solution for this question.

CREATE TABLE customers

( customer_id number(10) NOT NULL,

  customer_name varchar2(50) NOT NULL,

  city varchar2(50) NOT NULL,

  details varchar2(100) NOT NULL

);

insert into customers (customer_id, customer_name, city, details) values (111, 'name1', 'city1', 'ABC: 49680; ABC-EFG: IJK/IMH-88, AB-89');

insert into customers (customer_id, customer_name, city, details) values (112, 'name2', 'city2', 'ABC: 49680; EFG: 12345;');

insert into customers (customer_id, customer_name, city, details) values (113, 'name3', 'city3', '1.ABCD: 49106; IJK: COM-96 (Number)');

insert into customers (customer_id, customer_name, city, details) values (114, 'name4', 'city4', 'ABC: 49680; XYZ');

insert into customers (customer_id, customer_name, city, details) values (115, 'name5', 'city5', 'IJK: COM-96');

insert into customers (customer_id, customer_name, city, details) values (116, 'name6', 'city6', 'ABC: 49680');

commit;

Table:

I am using the below select statement, and im executing it in SQL Developer.

select * from customers where details in (&p_details);

During runtime, im entering value ''IJK: COM-96', 'ABC: 49680', 'IJK/IMH-88'' for the parameter &p_details which means all the records having these 3 values must be displayed, but its not happening. The output i get is,

The records with customer_id 111, 112, 113, 114 though have one of the run time parameter value, its not being fetched in the output. I understand it is because it has other characters or values included but is there a way, we can achieve this?

The output im trying to achieve is,

I dont want to use wild card search option which anyway wont help because i guess i wont be able to enter multiple values separated by a comma (eg. '%IJK: COM-96%', '%ABC: 49680%', '%IJK/IMH-88%')

Thanks for your help.

Thanks,

Pravin