Extract value from a text in oracle SQL

I have one column which has the below content

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))
(SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")
(SSL_SERVER_DN_MATCH=FALSE)))

Now I want to extract only zurich_WALLET_DIR from complete content and if MY_WALLET_DIRECTORY doesn't exist it should give me null value. Can someone help me achieve this?

Tried below but no success

SELECT REGEXP_REPLACE(HOST,'(.*)(MY_WALLET_DIRECTORY)[[:blank:]]*="[[:blank:]]*([^\)]+*)\"\).*','',1,1,'i') 
  FROM test;

You can use:

SELECT REGEXP_SUBSTR(
         HOST,
         '\(MY_WALLET_DIRECTORY\s*=\s*"(.*?)"\)',
         1,
         1,
         'i',
         1
       ) AS wallet_dir
from   test;

or, more to type but simple string functions are much faster than regular expressions:

SELECT CASE
       WHEN start_pos > 0 AND end_pos > 0
       THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
       END AS wallet_dir
FROM   (
  SELECT host,
         INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
         INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
  from   test
);

Which, for the sample data:

CREATE TABLE test (host) AS
SELECT '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))(SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")(SSL_SERVER_DN_MATCH=FALSE)))' FROM DUAL;

Both output:

If you just want the last part of the string then:

SELECT REGEXP_SUBSTR(
         HOST,
         '\(MY_WALLET_DIRECTORY\s*=\s*"[^"]*/(.*?)"\)',
         1,
         1,
         'i',
         1
       ) AS wallet_dir
from   test;

or:

SELECT SUBSTR(wallet_dir, INSTR(wallet_dir, '/', -1) + 1) AS wallet_dir
FROM   (
  SELECT CASE
         WHEN start_pos > 0 AND end_pos > 0
         THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
         END AS wallet_dir
  FROM   (
    SELECT host,
           INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
           INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
    from   test
  )
);

Which both output:

fiddle

Try this Regex :

select regexp_substr(HOST,'MY_WALLET_DIRECTORY=.*\/(.*)"\)',1,1,NULL,1) as Extracted
from mytable;

Result :

EXTRACTED
zurich_WALLET_DIR

Demo here