求Oracle REGEXP_REPLACE正则表达式清洗日期字段脏数据

Got it, let's tackle this problem step by step. You need to clean up invalid date strings in table A before inserting valid ones into table B's date column. Here's how you can approach it:

1. Regex Pattern for Valid Date Format #

First, let's define a regex that matches your target date format (d/m/yyyy or dd/mm/yyyy):

^\d{1,2}/\d{1,2}/\d{4}$

Breakdown:

• ^ = Start of the string
• \d{1,2} = 1 or 2 digits (for month/day)
• / = Literal slash separator
• \d{4} = 4-digit year
• $ = End of the string

This ensures we only keep strings that strictly follow the [1-2 digits]/[1-2 digits]/[4 digits] structure.

2. Using REGEXP_REPLACE to Clean Data #

You can use REGEXP_REPLACE to replace any string that doesn't match the valid pattern with NULL (since we can't convert invalid strings to a date). Then use TO_DATE to convert the valid strings:

INSERT INTO B (good_date)
SELECT 
    TO_DATE(
        -- Replace non-matching strings with NULL
        REGEXP_REPLACE(bad_date, '^(?!\d{1,2}/\d{1,2}/\d{4}$).*', NULL),
        'MM/DD/YYYY' -- Adjust this format mask if your dates are DD/MM/YYYY instead
    )
FROM A;

The regex ^(?!\d{1,2}/\d{1,2}/\d{4}$).* uses a negative lookahead to target any string that doesn't match our valid date pattern, replacing it with NULL.

3. Bonus: Validate Actual Date Validity #

Note that regex only checks the format, not if the date is logically valid (e.g., 2/30/2023 is a valid format but an invalid date). For Oracle 12c and above, use VALIDATE_CONVERSION to ensure the string can actually be converted to a date:

INSERT INTO B (good_date)
SELECT 
    CASE 
        WHEN VALIDATE_CONVERSION(bad_date AS DATE, 'MM/DD/YYYY') = 1
        THEN TO_DATE(bad_date, 'MM/DD/YYYY')
        ELSE NULL -- Or handle invalid dates as needed
    END AS clean_date
FROM A;

This is more robust than regex alone, as it catches both format and logical date errors.

Testing with Your Sample Data #

If you run either of the above queries with your sample data:

• '1/2/2005' will be converted to the date 02-JAN-2005 (or 01-FEB-2005 if you use DD/MM/YYYY format mask)
• 'indep' and '/' will be converted to NULL (so they won't insert invalid dates into table B)

内容的提问来源于stack exchange，提问作者user1751356