sqloracleoracle12c

Substr giving incorrect output in sql in oracle

I have table ot.plan:

which has

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

my expected output is:

 NAME                                                                            
    --------------------------------------------------------------------------------
    zzz_temp
    zzz_temp

I tried:

select SUBSTR(name,INSTR(name,'from')+5,INSTR(name,'b')) from ot.plan;

Its giving me output :

zzz_temp b where SUBSTR(a.hehri
zzz_temp b where SUBSTR(a.cline

How can I solve it?

Solution

  • You can achieve the desired result using the regexp_substr as follows:

    Select regexp_substr(name, 'from (.+) b',1,1,null,1)
  From ot.plan;