Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple wildcards in a IN expression

The_Cute_DBANov 15 2022 — edited Nov 15 2022

Hey Experts,
Got another question on another development research...

--RESEARCH_TABLE
CODE     PRICE      NOTATION
-------- ---------- -----------
100      10         ABC1_A
100      14         ABC2_B
100      20         ABC3_C
100      33         ABC4_D
200      90         ABZ1_A
200      76         ABZ2_B
200      45         ABZ3_C
200      19         ABZ4_D
300      11         ABY1_1
300      20         ABY2_2
300      55         ABY3_3
400      90         ABX1_1
400      34         ABX2_2
500      56         ABX3_3
..
..
..

NOTATION values shown above are all the possible values in this table. Using a regular select statement, is there are way for me to select multiple wildcards in a LIKE statement keeping my code as short as possible?
What I know is to code it like this:

select * from RESEARCH_TABLE
where notation like 'ABC1%'
or notation like 'ABZ1%'
or notation like 'ABY1%'
or notation like 'ABX1%';

One may say just do: where notation like 'AB%' but I am only trying to explain the thought.
My question is: Is there a way to code this like the idea shown below:
select * from RESEACH_TABLE
where notation "IN" ('ABC1%','ABZ1%', 'ABY1%', 'ABX1%');
Is there a way to write an Oracle SQL select statement using the idea above?

Comments
Post Details
Added on Nov 15 2022
8 comments
2,586 views