How to extract specific part of column using REGEXP in oracle?

Question

I have a table plan and it has following data stored as:

NAME                                                                            
--------------------------------------------------------------------------------
EXISTS(SELECT 1 from a where SUBSTR(a.hehrircal,1,4)=b.acc_num                  
EXISTS(SELECT 1 from a where a.group_id=b.acc_num

I want to extract the part after where to =.My expected output is:

 NAME                                                                            
--------------------------------------------------------------------------------
SUBSTR(a.hehrircal,1,4)                  
a.group_id

So,I tried like this::

select REGEXP_REPLACE(name,'^[EXISTS(SELECT 1 from a where]',1,6) from ot.plan;

But its not working.Is it possible to extract such part in oracle?

Answer 1

You can use substr and instr as following:

SQL> with your_Data as
  2  (select 'EXISTS(SELECT 1 from a where SUBSTR(a.hehrircal,1,4)=b.acc_num' as str from dual)
  3  SELECT SUBSTR(STR, INSTR(STR, 'where') + 5, INSTR(STR, '=') -(INSTR(STR, 'where') + 5))
  4  FROM YOUR_DATA;

SUBSTR(STR,INSTR(STR,'WH
------------------------
 SUBSTR(a.hehrircal,1,4)

SQL>

Cheers!!