Oracle regex pick value in pipe delimiter string-CodePudding

I want to get the result OK when i run below query in oracle

select (regexp_matches('TType=SEND|Status=OK','Status=([^|] )'))[1] as status --FROM dual

The query works in postgresql, how do i do it in Oracle.

CodePudding user response：

Test strings in with clause (not part of the actual query); notice a few special cases and how they are handled. If different handling is needed, that can be accommodated - just say what the required handling is in those cases.

with
  test_data (str) as (
    select 'TType=SEND|Status=OK'  from dual union all
    select 'String with no Status' from dual union all
    select 'Status=LAST|Time=Now'  from dual union all
    select 'This Status=Good|X=65' from dual union all
    select 'Ttype=SEND|Status='    from dual
  )
select str, regexp_substr(str, 'Status=([^|] )', 1, 1, null, 1) as status
from   test_data
;

STR                     STATUS               
---------------------   ---------------------
TType=SEND|Status=OK    OK                   
String with no Status                      
Status=LAST|Time=Now    LAST                 
This Status=Good|X=65   Good                 
Ttype=SEND|Status=

https://docs.oracle.com/database/121/SQLRF/functions164.htm#SQLRF06303

CodePudding user response：

Maybe you can try like this :

SELECT SUBSTR(REGEXP_SUBSTR('TType=SEND|Status=OK','Status=([^|] )'),8) 
AS "status" FROM dual

you can hide double quote (") on status if still error