RegEx matching a valid date Presto

Question

For the table below, I'd like to extract the available from date from the additional_info string. I know there is a regexp_extract_all(string, pattern) function in Presto but not quite sure how to extract a date from string.

FYI:

• Dates are always in same format
• There can only be one date in the additional_info column
• Rows with no date in additional_info column have null in date column

table

product_id   additional_info
325245       New, contact for more information, available from 01.01.2020
635255       Used, available from 06.11.2020
422632        New, contact for more information

desired output_table

product_id     available_date 
325245         01.01.2020
635255         06.11.2020
422632

Answer 1

If you're dates are always in this format, a very straightforward solution could be:

SELECT
   product_id
   , regexp_extract(additional_info, '(\d\d.\d\d.\d\d\d\d)')
FROM table

It will return the first substring matched by your capturing group (betweenbrackets).