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?

halfer · Accepted Answer · 2025-04-29 19:31:14Z

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>

edited Apr 29 at 19:31

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Mar 11, 2020 at 14:25

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2020-03-11 14:26:36Z

1

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

answered Mar 11, 2020 at 14:26

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

Random guy Over a year ago

what does select INSTR( LOWER(name), '=',23 ) from ot.plan; do?

MT0 Over a year ago

@Randomguy INSTR finds the position of the next = character in the name column and skips the first 22 characters and starts looking from the 23rd. But you could also have found that out by searching for the INSTR documentation

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related