How to extract part of string in Google Big Query by using StandardSQL

Question

my hit.product.ProductSku value is in XXXXX-1234-2017-02-27-A pattern. I would like to extract the 2017-02-27 which is a date from that value.

I tried to use Substring, Charindex, trim, left, but those functions are not available.

Anyone have any idea how to do it? I am using Standard SQL.

Thank you

Answer 1

Try

#standardSQL
SELECT PARSE_DATE('%Y-%m-%d', REGEXP_EXTRACT('XXXXX-1234-2017-02-27-A', r'\w+-\d+-(.*)-\w'))

I am using here very wide pattern based on what is presented in your question - but you might want to improve it. something like below , and so on

#standardSQL
SELECT PARSE_DATE('%Y-%m-%d', REGEXP_EXTRACT('XXXXX-1234-2017-02-27-A', r'\w+-\d+-(\d{4}-\d{2}-\d{2})-\w'))