Multiple string check in oracle db

Question

Say I want to check if a title name contains "stack" or contains "over" or contains "flow". Can I shorten the below code?

select name 
from Title
where name like '%stack%' or name like '%over%' or name like '%flow%'
;

Thanks!

Can't find an obvious way to shorten the query using LIKE. You could use REGEXP_LIKE, however that may have performance implications so may not necessarily be a good idea just to type less SQL. Let me know if you want me to add an example as an answer. — Joachim Isaksson
– Joachim Isaksson, Commented Mar 14, 2016 at 5:03
Yeah I can try that, if it's not a huge hindrance on speed then it should be okay. — bZhang
– bZhang, Commented Mar 14, 2016 at 5:13

Joachim Isaksson · Accepted Answer · 2016-03-14 05:45:07Z

1

You could use REGEXP_LIKE, however that may have performance implications, so you may want to test it for your specific use case;

SELECT name 
FROM Title
WHERE REGEXP_LIKE(name, '.*(stack|over|flow).*')

...or if you want the match case insensitive you can pass i as a third parameter;

SELECT name 
FROM Title
WHERE REGEXP_LIKE(name, '.*(stack|over|flow).*', 'i');

answered Mar 14, 2016 at 5:45

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1