Select only the latest occurance of a date. Regex-CodePudding

Select only the latest occurrence of a date. \d{2}([.-/])\d{2}\1\d{4}

1)07/06/2022 refusal
2)policy of other SK, until 01.03.2027, 14904
3)05/19/2021, providing an insurance contract with another insurance company 9522 until 05/20/2022, until 05/20/2023
4)12/04/2020, granted by 8639 to 10/10/2024

Expected Result:

1)07/06/2022
2)01.03.2027
3)05/20/2023
4)10/10/2024

CodePudding user response：

Assuming that the dates always appear in the text in ascending order from start to finish, you then would be asking for the last date to appear in each text. In that case, we can use a negative lookahead to identify the last date:

\d{2}([.-/])\d{2}\1\d{4}(?!.*\d{2}([.-/])\d{2}\2\d{4})
                         ^^^ ensures that no other date follows the match

Demo

Edit:

To handle this requirement on Oracle SQL, which does not support lookarounds, we need to get creative. One approach would be to reverse the string, then find the first date, and then reverse that value. Using REVERSE() along with REGEXP_SUBSTR() we can try:

WITH t AS (
    SELECT '05/19/2021, providing an insurance contract with another insurance company 9522 until 05/20/2022' AS val
    FROM dual
)

SELECT REVERSE(REGEXP_SUBSTR(REVERSE(val), '(\d{4}[./-]\d{2}[./-]\d{2})'))
FROM t;  -- 05/20/2022