Moudiz Moudiz - 6 months ago 20
SQL Question

REGEXP_SUBSTR where in condition

I have this data in a table

procedure1/loc1/p1
proc2/loc1/p2/c1
proc1/loc2/p2/c2
procedure3/loc1/p1
procedure4/loc3/p1


I want a query to select specific proc, like proc1 and procedure4 ,
like
wouldnt work so
the ouptput should be like this

proc1/loc2/p2/c2
procedure4/loc3/p1


I tried to use REGEXP_SUBSTR, but how include the list of the procedure that i want ?

--this is wrong
select * from tab1 where REGEXP_SUBSTR(col1,
'[^/]+', 1, 1) in ('proc1','procedure4')

Answer

you could check, if the length returned by REGEXP_SUBSTR is greater than 0

select *
from (select 'procedure1/loc1/p1' a from dual union
      select 'proc2/loc1/p2/c1' from dual union
      select 'proc1/loc2/p2/c2' from dual union
      select 'procedure3/loc1/p1' from dual union
      select 'procedure4/loc3/p1' from dual) t
where length(regexp_substr(t.a, 'procedure4|proc1')) > 0

alternativly you could use REGEXP_LIKE, which does simply return a boolean and in my oppinion would fit better here.

select *
from (select 'procedure1/loc1/p1' a from dual union
      select 'proc2/loc1/p2/c1' from dual union
      select 'proc1/loc2/p2/c2' from dual union
      select 'procedure3/loc1/p1' from dual union
      select 'procedure4/loc3/p1' from dual) t
where regexp_like(t.a, 'procedure4|proc1')

O/P

proc1/loc2/p2/c2
procedure4/loc3/p1

if you would want to get the values from a table you could generate the regex dynamicly with the listagg function provided by oracle. What happens now is, that each possible value, that could occure is getting concatinated with an |, which does represent an or in regex. Due to this you you're not in need for an in, because your regex will have each possible value seperated by an or

select *
from (select 'procedure1/loc1/p1' a from dual union
      select 'proc2/loc1/p2/c1' from dual union
      select 'proc1/loc2/p2/c2' from dual union
      select 'procedure3/loc1/p1' from dual union
      select 'procedure4/loc3/p1' from dual) t
where regexp_like(t.a, (select listagg(regexp.b, '|') WITHIN GROUP (ORDER BY regexp.b) regex
                        from   (select 'procedure4' b from dual union
                                select 'proc1'        from dual) regexp))

O/P of the subquery used for the regex would be proc1|procedure4, which would be the regexp needed as seen in the previous example

Comments