你可以使用简单的字符串函数：

SELECT  mons_id,
        cif_id,
        CASE
        WHEN name_pos  > 0 AND value_pos > name_pos
        THEN SUBSTR(details, 1, name_pos - 1)
             || 'MONS ID'
             || SUBSTR(details, name_pos + 6, value_pos - name_pos - 6)
             || mons_id
             || SUBSTR(details, value_pos + LENGTH(cif_id))
        ELSE details
        END AS details
FROM    (
  SELECT t.*,
         INSTR(LOWER(details), 'cif id') AS name_pos,
         INSTR(details, cif_id) AS value_pos
  FROM   table_name t
);

或者使用正则表达式（输入更少但更慢）：

SELECT  mons_id,
        cif_id,
        REGEXP_REPLACE(
          details,
          'cif id(.*?)' || cif_id,
          'MONS_ID\1' || mons_id,
          1,
          0,
          'i'
        ) AS details
FROM    table_name;

其中，对于样本数据：

CREATE TABLE table_name (mons_id, cif_id, details) AS
SELECT 'ASD345', 1234567,   'CIF ID 1234567 xyz' FROM DUAL UNION ALL
SELECT 'ASD569', 345245254, 'abc CIF ID: 345245254 xyz' FROM DUAL UNION ALL
SELECT 'ASD508', 64647474,  'efg CIF ID mno 64647474 uvw' FROM DUAL UNION ALL
SELECT 'ASD402', 12097655,  'hij klm' FROM DUAL;

两个输出：
| MONS_ID| CIF_ID|细节|
| --|--|--|
| ASD345| 1234567 |MONS_ID ASD 345 xyz|
| ASD569| 345245254 |abc MONS_ID：ASD569 xyz|
| ASD508| 64647474 |efg MONS_ID mno ASD 508 uvw|
| ASD402| 12097655 |希杰河|
fiddle