regexp substr - Extract date from string oracle

Question

I have a column in which a string starts with - 'Chicago, IL, April 20, 2015 — and so on text here'. I want to extract the Date part from this string in Oracle. Any ideas on how to do this. I was able to find something for mm/dd/yyyy like below, but not for long date format.

SELECT REGEXP_SUBSTR(' the meeting will be on 8/8/2008', '[0-9]{1,}/[0-9]{1,}/[0-9]{2,}') FROM dual 

Answer 1

You could use:

SELECT  TO_DATE(
          REGEXP_SUBSTR(
            'Chicago, IL, April 20, 2015 — and so on text here',
            '(JANUARY|FEBRUARY|MARCH|APRIL|MAY|JUNE|JULY|AUGUST|SEPTEMBER|'
              || 'OCTOBER|NOVEMBER|DECEMBER)'
              || '[[:space:]]+([012]?[0-9]|3[01])'
              || '[[:punct:][:space:]]+d{4}',
            1,
            1,
            'i'
          ),
          'MONTH DD YYYY'
        )
FROM    DUAL;

If you want to validate the dates as well (so you don't get an error for February 29, 2001) then you could use a user-defined function:

CREATE FUNCTION parse_Date(
  in_string     VARCHAR2,
  in_format     VARCHAR2 DEFAULT 'YYYY-MM-DD',
  in_nls_params VARCHAR2 DEFAULT NULL
) RETURN DATE DETERMINISTIC
AS
BEGIN
  RETURN TO_DATE( in_string, in_format, in_nls_params );
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

And replace the TO_DATE( ... ) function with PARSE_DATE( ... )