How to generalize '2021/11/27' in CASE-CodePudding

I'm a beginner.

I'm working on the following Query but it's not working well. Could someone help me?

This is the raw data.

I only want to take out of date such as '11/27/2021' from here. Not '100g' nor null.

Data type is STRING.

And my query is this.

SELECT
    CASE WHEN 
    date = 'm/%d/%Y' then PARSE_DATE("%Y/%m/%d", expiry_date) 
    Else null 
    end as date,
FROM
    my_database

I'm looking forward to your advise. Have a good day!

CodePudding user response：

Consider below few options

select safe.parse_date('%m/%d/%y', date) as date
from my_database

select 
 case 
   when regexp_contains(date, r'\d{1,2}/\d{1,2}/\d{2}') then parse_date('%m/%d/%y', date)
    else null
  end as date
from my_database

I recommend first option, but both return same output.

You can test with dummy data

with my_database as (
  select '11/27/21' as date union all 
  select '100m' union all
  select '' union all
  select null union all
  select '12/4/21' union all
  select '120g' 
)

output is