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!!

Answer 2

You don't need (slow) regular expressions and can use simple string functions:

SELECT SUBSTR( name, start_pos, end_pos - start_pos ) AS match
FROM   (
  SELECT name,
         INSTR( LOWER(name), ' where ' ) + LENGTH( ' where ') AS start_pos,
         INSTR( name, '=', INSTR( LOWER(name), ' where ' ) ) AS end_pos
  FROM   table_name
)

So, for your sample data:

CREATE TABLE table_name ( NAME ) AS
SELECT 'EXISTS(SELECT 1 from a where SUBSTR(a.hehrircal,1,4)=b.acc_num' FROM DUAL UNION ALL
SELECT 'EXISTS(SELECT 1 from a where a.group_id=b.acc_num' FROM DUAL

This outputs:

| MATCH                   |
| :---------------------- |
| SUBSTR(a.hehrircal,1,4) |
| a.group_id              |

db<>fiddle here