-2

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})$')        ) 
             )
7
  • 1. You can mutualize your expressions that explore both / and - by using only one regex with [-/]. 2. Tell the results you expect. In 7-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? Commented yesterday
  • 1
    @GuillaumeOutters 7-03 could also be July 2003 or March 2007 or 7th day of 2003 or 03rd day of 2007 or ....... So, as you point out, the OP needs to clarify the exact behaviours for all possible scenarios. Commented yesterday
  • For extracting sub-strings matching the pattern DD-MM or MM-YY or MM-YYYY then you can use: fiddle Commented yesterday
  • 3
    @MatBailie There are two problems that are being discussed in comments: 1) extracting sub-strings matching certain patterns; 2) converting those sub-strings to dates. The OP is not asking to solve the second problem (converting strings to dates) and they only want the first problem to be solved as they are looking to filter the results based on the patterns (they don't technically even want the matches to be returned, only to know that they exist). Commented yesterday
  • 1
    If I am not mistaken, none of the shown strings contains a date. Some values (03/2022, 03-2021) may represent a month or a quarter or the like, but I don't see any date. Commented yesterday

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.