How to use REGEX in bigquery for a string

Question

I am having trouble extracting a string in my dataset using REGEXP_EXTRACT in bigquery. Below are my strings and desired outputs:

STRING: Gas Safe_160x600_v2b   DESIRED OUTPUT: 160X600
STRING: Gas Safe_60x150_v2b   DESIRED OUTPUT: 60X150
STRING: Gas Safe_100x40_v2b   DESIRED OUTPUT: 100X40

Can anyone help?

Mikhail Berlyant · Accepted Answer · 2019-12-16 19:22:42Z

1

Below is for BigQuery Standard SQL

Few options are using SPLIT or REGEXP_EXTRACT as in below example

#standardSQL
SELECT text, 
  SPLIT(text, '_')[SAFE_OFFSET(1)] extracts1,
  REGEXP_EXTRACT(text, r'_(.*)_') extracts2
FROM `project.dataset.table`

If to apply to sample data in your question - result is

Row text                    extracts1   extracts2    
1   Gas Safe_160x600_v2b    160x600     160x600  
2   Gas Safe_60x150_v2b     60x150      60x150   
3   Gas Safe_100x40_v2b     100x40      100x40

answered Dec 16, 2019 at 19:22

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Comments

Erwan Sturzenegger · Accepted Answer · 2019-12-17 06:27:54Z

0

You should use this expression:

([0-9]+x[0-9]+)

edited Dec 17, 2019 at 6:27

answered Dec 16, 2019 at 14:56

Erwan Sturzenegger

212 bronze badges

Collectives™ on Stack Overflow

How to use REGEX in bigquery for a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related