Moudiz - 9 months ago 32

SQL Question

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`

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