CODEWITHSUNDEEP
regexgoogle-bigquery
Michael
Michael

Reputation: 1728

Regex match everything after first and until 2nd occurrence of a slash

Need to match everything after the first / and until the 2nd / or end of string. Given the following examples:

/US
/CA
/DE/Special1
/FR/Special 1/special2

Need the following returned:

US
CA
DE
FR

Was using this in DataStudio which worked:

^(.+?)/

However the same in BigQuery is just returning null. After trying dozens of other examples here, decided to ask myself. Thanks for your help.

Upvotes: 0

Views: 2863

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

For such simple extraction - consider alternative of using cheaper string functions instead of more expensive regexp functions. See an example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '/US' line UNION ALL
  SELECT '/CA' UNION ALL
  SELECT '/DE/Special1' UNION ALL
  SELECT '/FR/Special 1/special2' 
)
SELECT line, SPLIT(line, '/')[SAFE_OFFSET(1)] value 
FROM `project.dataset.table`

with result 

Row line                    value    
1   /US                     US   
2   /CA                     CA   
3   /DE/Special1            DE   
4   /FR/Special 1/special2  FR

Upvotes: 2

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626871

Your regex matches any 1 or more chars as few as possible at the start of a string (up to the first slash) and puts this value in Group 1. Then it consumes a / char. It does not actually match what you need.

You can use a regex in BigQuery that matches a string partially and capture the part you need to get as a result:

/([^/]+)

It will match the first occurrence of a slash followed with one or more chars other than a slash placing the captured substring in the result you get.

Upvotes: 2

Related Questions