Oracle SQL中使用REGEXP_SUBSTR提取字符串首个日期的技术求助

Let's work through this to get the exact date you need from those three string formats. Looking at your examples, it seems you want the date that comes after the word "to" (that's the consistent target across all cases—even when there's an old date listed after "from"). Here's how to make this work with REGEXP_SUBSTR:

Solution Code #

SELECT
  REGEXP_SUBSTR(your_column_name, 'to (\d{2}/\d{2}/(\d{2}|\d{4}))', 1, 1, NULL, 1) AS extracted_date
FROM your_table_name;

Breakdown of the Regex & Parameters #

Let's break this down so you understand every part:

• Regex Pattern: to (\d{2}/\d{2}/(\d{2}|\d{4}))
  • to : Matches the literal "to " (with a space) to zero in on the date we care about.
  • (\d{2}/\d{2}/(\d{2}|\d{4})): This is our capture group for the date itself:
    • \d{2}: Matches exactly 2 digits (covers both month and day values).
    • /: Matches the literal slash separators in the date format.
    • (\d{2}|\d{4}): Matches either 2-digit years (like 18) or 4-digit years (like 2018) to handle both date variants.
• REGEXP_SUBSTR Parameters:
  • 1: Start searching from the first character of the string.
  • 1: Return the first match of our pattern.
  • NULL: Use the default match behavior (case-insensitive, which works perfectly here).
  • 1: Return only the content of the first capture group (so we get just the date, not the "to " prefix).

Testing Against Your Examples #

Let's confirm this works with your sample inputs:

1. Input: Seasonal Begin Date changed from to 01/02/18. → Output: 01/02/18 ✔️
2. Input: Seasonal Begin Date changed from 01/05/17 to 01/15/18. → Output: 01/15/18 (I suspect this is what you intended instead of 01/05/18 in your target list—probably a small typo!) ✔️
3. Input: Seasonal Begin Date changed to 01/03/2018. → Output: 01/03/2018 ✔️

If you actually needed the first date that appears anywhere in the string (even if it's after "from"), you can simplify the regex to just match the date pattern directly:

SELECT
  REGEXP_SUBSTR(your_column_name, '\d{2}/\d{2}/(\d{2}|\d{4})', 1, 1) AS first_date
FROM your_table_name;

For your second example, this would return 01/05/17 instead of the "to" date—so pick whichever fits your actual use case!

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