oracle query - how to combine like and in clause

I have to write a sql statement similar to in clause but I dont know the exact value of type so I want to do like clause but can I have more than 1 matching string?

for example type value is flash MB, stream MB, traffic MB

I want to write select * from stats where type like (%Fl%MB,%str%MB,Traf%MB)
Who is Participating?
sdstuberConnect With a Mentor Commented:
or use regular expressions but you still must list each

where regexp_like(type,'(Fl|str|Traf).*MB')
you can't

do it as a multiple  or clauses

where type like '%Fl%MB'
or type like '%str%MB'
or type like 'Traf%MB'
Devinder Singh VirdiLead Oracle DBA TeamCommented:
I believe you can also use XML to write dynamic where clause.
I am not good in XML, Stuber can help in that.
Something like :-

XMLTABLE( DBMS_XMLGEN.getxml('select * from TEMP_TABLE where &cond') )
from dual
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

sure, it would be possible, but that would be REALLY inefficient as you'd be constructing the same set of OR clauses or regexp clauses,  or worse... multiple queries, one for each condtion
then turning those results into xml, then you'll have to parse the xml to get your columns back.

so, yes, I "could" write that, but I won't because it's definitely not the right way to go
mahjagAuthor Commented:
will this capture before and after like %fl% - ?
it should,

easiest way to check is to try it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.