CODEWITHSUNDEEP
regexgoogle-bigquery
Suhayb Dar
Suhayb Dar

Reputation: 101

How to use REGEX in bigquery for a string

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?

Upvotes: 0

Views: 111

Answers (2)

Erwan Sturzenegger
Erwan Sturzenegger

Reputation: 21

You should use this expression: 

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 1

Related Questions