Oracle like with AND query example

This article explains how to write the oracle sql queries with like then AND query together.

Oracle like with AND query example – Use Case / Scenario

Need to find the list of coupons by title containing 50 and also the text recharge.

We would like to get the result something like this by passing “50” and “recharge” as like condition wiith AND,

• Rs. 50/- offer for recharge.
• Upto 50% offer on prepaid recharge.
• Upto 5% cashback for billpayments and recharges.

Like & AND oracle together- Way 1:

select * from coupons where coupon_title like '%50%' and coupon_title like '%recharge%';

Like & AND oracle together – Way 2:

select * from coupons where coupon_id in (
select coupon_id from coupons where Regexp_Like (coupon_title,'50')) and 
Regexp_Like (coupon_title, 'recharge');

Things to Note:

• Regexp_Like – Regular expression like condition in oracle.
• You are not required to install any other plugins/tools, it works by default.
• If you note, I did not include “%” in the condition mentioned only ’50’ and ‘recharge’.