Big Query Extracting date from string

Question

| orders |

|sold [2022-06-09 to 2022-06-17] |

What I am trying to get is

orders	date1	date2
sold [2022-06-09 to 2022-06-17]	2022-06-09	2022-06-17

select
    Orders, REGEXP_EXTRACT_ALL(Orders,r'([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])') dates
from table

But BQ gives me the result in one row then split into 2 rows

Row	Orders	Dates
1	text with dates	2022-06-09
		2022-09-17

when exported to csv it is

Orders	Dates
text with dates	[2022-06-09,2022-06-27]

I have tried to select left/right to get the fields I want but get an array error from Big Query

Pointers Appreciated

Mikhail Berlyant · Accepted Answer · 2022-07-06 00:08:53Z

3

Use below approach

select Orders, dates[offset(0)] as date1, dates[offset(1)] as date2
from table, 
UNNEST([struct(REGEXP_EXTRACT_ALL(Orders,r'([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])') as dates)])

if applied to sample data in your question - output is

answered Jul 6, 2022 at 0:08

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Big Query Extracting date from string

| orders |

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

| orders |

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related