Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE auditlog (
  auditid          VARCHAR2(36),
  searchableattrib BLOB CHECK (searchableattrib IS JSON)
);
INSERT INTO auditlog (
  auditid,
  searchableattrib
) VALUES (
  '3622fe86-0db7-4c79-afb1-b7cdcc99c0e4',
  '{"newValue":"#01D","fieldName":"U_MP_REASON_CODE","oldValue":"#00","scenarioName":"LIVE","pageName":"SKU WORKSPACE","intersectionKey":"LOC: FR01, ITEM: 16651903"}'
);
1 rows affected

SELECT auditid,
       SUBSTR(intersectionKey, INSTR(intersectionKey, 'ITEM: ') + 6) AS item
FROM   auditlog
       CROSS APPLY JSON_TABLE(
         searchableattrib,
         '$'
         COLUMNS (
           intersectionKey VARCHAR2(200) PATH '$.intersectionKey'
         )
       )
WHERE auditid = '3622fe86-0db7-4c79-afb1-b7cdcc99c0e4'


AUDITID
ITEM


3622fe86-0db7-4c79-afb1-b7cdcc99c0e4
16651903


SELECT auditid,
       SUBSTR(intersectionKey, INSTR(intersectionKey, 'ITEM: ') + 6) AS item
FROM   (
  SELECT auditid,
         JSON_VALUE(searchableattrib, '$.intersectionKey') AS intersectionKey
  FROM   auditlog
  WHERE auditid = '3622fe86-0db7-4c79-afb1-b7cdcc99c0e4'
)


AUDITID
ITEM


3622fe86-0db7-4c79-afb1-b7cdcc99c0e4
16651903