I have a case where I need to filter for the partial date string from a column.
Here is the input:
date_value
-----------------------------
7-03 start 4444
03/2022 follow up
1111 7-03 03/2022 something
kk 03-2021 test 3333
follow up 15/12 note
7-03 start 3333
03/2022 follow up
1111 7-03 03/2022 something
kk 03-2021 test 3333
follow up 15/12 note
14-2627272 15/01
This is the code I tried, but I'm wondering how I can handle any exceptional case where partial date may appear in any format:
SELECT date_value
FROM notes_test r
WHERE EXISTS (SELECT 1
FROM (SELECT REGEXP_SUBSTR(r.date_value, '[^ ]+', 1, LEVEL) AS token
FROM dual
CONNECT BY LEVEL <= LENGTH(r.date_value) - LENGTH(REPLACE(r.date_value, ' ', '')) + 1) t
WHERE REGEXP_COUNT(t.token, '[/-]') = 1
AND (REGEXP_LIKE(t.token, '^([0-2]?[0-9]|3[01])/(0?[1-9]|1[0-2])$')
OR REGEXP_LIKE(t.token, '^([0-2]?[0-9]|3[01])-(0?[1-9]|1[0-2])$')
OR REGEXP_LIKE(t.token, '^(0?[1-9]|1[0-2])/([0-9]{2,4})$')
OR REGEXP_LIKE(t.token, '^(0?[1-9]|1[0-2])-([0-9]{2,4})$') )
)
/and-by using only one regex with[-/]. 2. Tell the results you expect. In7-03 03/2022, do you want both July 3rd (of the current year?) and (1st of) March 2022? Only one of them? Then which one is judged "more a date" than the other?7-03could also beJuly 2003orMarch 2007or7th day of 2003or03rd day of 2007or ....... So, as you point out, the OP needs to clarify the exact behaviours for all possible scenarios.