• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

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)
0
mahjag
Asked:
mahjag
  • 4
1 Solution
 
sdstuberCommented:
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'
0
 
sdstuberCommented:
or use regular expressions but you still must list each


where regexp_like(type,'(Fl|str|Traf).*MB')
0
 
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 :-

select
XMLTABLE( DBMS_XMLGEN.getxml('select * from TEMP_TABLE where &cond') )
from dual
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sdstuberCommented:
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
0
 
mahjagAuthor Commented:
regexp_like(type,'(Fl|str|Traf).*MB')
will this capture before and after like %fl% - ?
0
 
sdstuberCommented:
it should,

easiest way to check is to try it
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now