如何在Oracle SQL查询语句中替换字符串内的日期参数为SYSDATE对应值

云原生阿岚

2026-4-30

Replacing Date Placeholders with SYSDATE Values in Oracle SQL

Got it, let's tackle this problem step by step. You want to swap those %y%m%d and %H%H date placeholders in your string column with actual values pulled from SYSDATE—here's how to do it cleanly in a SELECT query:

Core Approach: Nested `REPLACE` with `TO_CHAR`

Oracle's REPLACE function lets you swap specific substrings, and TO_CHAR converts SYSDATE into the exact string format you need for each placeholder. You'll nest multiple REPLACE calls to handle each placeholder one by one.

Example Query

Suppose your column is named file_name and your table is document_records. Here's the working query:

SELECT 
  REPLACE(
    REPLACE(file_name, '%y%m%d', TO_CHAR(SYSDATE, 'YYMMDD')),
    '%H%H', TO_CHAR(SYSDATE, 'HH24')
  ) AS formatted_file_name
FROM document_records;

Let's Break This Down

TO_CHAR(SYSDATE, 'YYMMDD'): Converts SYSDATE to a 6-character string like 240520 (for May 20, 2024). Swap YY with YYYY if you need a 4-digit year (e.g., 20240520).
TO_CHAR(SYSDATE, 'HH24'): Converts SYSDATE's hour to a 2-digit 24-hour format (e.g., 14 for 2 PM, 09 for 9 AM).
Nested REPLACE: First replaces %y%m%d with the date string, then takes that result and replaces %H%H with the hour string. The order doesn't matter here—you could swap the inner and outer REPLACE calls and get the same outcome.

Handling Extra Placeholders

If you have additional placeholders like %M%M (minutes) or %S%S (seconds), just add more nested REPLACE calls:

SELECT 
  REPLACE(
    REPLACE(
      REPLACE(file_name, '%y%m%d', TO_CHAR(SYSDATE, 'YYMMDD')),
      '%H%H', TO_CHAR(SYSDATE, 'HH24')
    ),
    '%M%M', TO_CHAR(SYSDATE, 'MI')
  ) AS formatted_file_name
FROM document_records;