0

I have a query including connect by prior like;

SELECT SYS_CONNECT_BY_PATH(ENAME, '/') as path
FROM EMP
WHERE CONNECT_BY_ISLEAF=1
START WITH MGR IS NULL CONNECT BY
PRIOR EMPNO=MGR;

The result is following 

 path
-----------------------------------
/KING/JONES/SCOTT/ADAMS
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/JAMES
/KING/CLARK/MILLER

Now I want to apply regular expression(REGEXP_LIKE in Oracle) to path column to find '/KING/JONES/SCOTT/ADAMS' by '/KING/JONE'.

Is there a way to do it?

Thanks in advance

user6493966
  • 73
  • 2
  • 8
  • 1
    Obviously there isn't, it will also find the second row ('KING/JONES/FORD/SMITH'). Also, if you have a problem that makes sense (this one doesn't), why do you only want solutions with REGEXP_LIKE and not any other methods? –  Oct 26 '16 at 11:10
  • I am sorry for this .You are right.I changed the query result set.Please think over the last result set.Regexp_like or other methods can help me. – user6493966 Oct 26 '16 at 11:13

1 Answers1

2

Use a CTE:

with t as (
  SELECT SYS_CONNECT_BY_PATH(ENAME, '/') as path
  FROM EMP
  WHERE CONNECT_BY_ISLEAF=1
  START WITH MGR IS NULL CONNECT BY
  PRIOR EMPNO=MGR
)
select *
from t
where path like '/KING/JONE%';

(in your example, like will do the job just fine - and faster than regexp_like)

Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24