Error Handling when converting text to dates-CodePudding

Lets says I have a column which has text and most of them are in a particular format which allows me to scrape out the date part. However, some of the texts does not have dates and I want it to return NULL rather than scraping it. How would I go about doing this in Redshift (SQL)?

Current Output:

CAST(REPLACE(SUBSTRING(c."name",3,9),'.','-') AS DATE)

Some fields are like the below and I need the code to return a null when it cannot convert the substring to a date datetype

Example:

Error:

CodePudding user response：

You can use pattern matching and case when for such cases

select case
           when c."name" similar to '%\\d{2}.\\d{2}.\\d{2}%'
               then CAST(REPLACE(SUBSTRING(c."name", 3, 9), '.', '-') AS DATE)
           else null
           end as cast_date;