Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table_name (value) AS
SELECT 'if :P4_VARCHAR_1 = "1"' FROM DUAL UNION ALL
SELECT 'if :VARCHAR_1 = "1"' FROM DUAL
2 rows affected

SELECT REGEXP_REPLACE(
         value,
         ':([^P]|P[^0-9_])(\w+)',
         '1'
       ) AS output
FROM   table_name;


OUTPUT


if :P4_VARCHAR_1 = "1"

if 1 = "1"


SELECT REGEXP_REPLACE(
         value,
         ':([^P]|P[^[:digit:]_])(\w+)',
         '1'
       ) AS output
FROM   table_name;


OUTPUT


if :P4_VARCHAR_1 = "1"

if 1 = "1"