By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';
CREATE TABLE table_name (value) AS
SELECT '09/23/2022 12:44 PM EDT' FROM DUAL UNION ALL
SELECT '06/23/2022 12:44 PM EDT' FROM DUAL UNION ALL
SELECT '12/23/2022 12:44 PM EDT' FROM DUAL;
3 rows affected
SELECT TO_TIMESTAMP_TZ(
REGEXP_REPLACE(
value,
'(EDT|EST)$',
'US/EASTERN'
),
'MM/DD/YYYY HH12:MI AM TZR'
) AS tz
FROM table_name;
| TZ |
|---|
| 2022-09-23 12:44:00.000000000 -04:00 |
| 2022-06-23 12:44:00.000000000 -04:00 |
| 2022-12-23 12:44:00.000000000 -05:00 |