Big Query Regexp_Extract using Google Analytics url

Question

How do I extract the id parameter below using Big Query Regexp_Extract some rows with page urls in them that look similar to : url.com/id=userIDmadeUPofletterandnumbers&em=MemberType eg url.com/id=asd1221231sf&em=studentMember

I have tried using: a. REGEXP_EXTRACT(urlValue,"id=\w+") as Idvalue but I get the error message: Invalid string literal: "id=\w+"

I am pretty close with this: REGEXP_EXTRACT(urlValue,"(id=.*&em)") however it shows me id=asd1221231sf&em and I want to exclude id= and &em at the end

Mikhail Berlyant · Accepted Answer · 2018-07-14 00:15:48Z

3

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'url.com/id=userIDmadeUPofletterandnumbers&em=MemberType' urlValue UNION ALL
  SELECT 'url.com/id=asd1221231sf&em=studentMember'
)
SELECT REGEXP_EXTRACT(urlValue, r'id=(\w+)') id, urlValue
FROM `project.dataset.table`

Row id                              urlValue     
1   userIDmadeUPofletterandnumbers  url.com/id=userIDmadeUPofletterandnumbers&em=MemberType  
2   asd1221231sf      url.com/id=asd1221231sf&em=studentMember

answered Jul 14, 2018 at 0:15

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

2 Comments

UKDataGeek Over a year ago

Thanks ! Any idea why adding that extra r at the beginning of the Regex makes it work - I can't see a link to it in the documentation

Mikhail Berlyant Over a year ago

those are so called raw strings you can check them here

Collectives™ on Stack Overflow

Big Query Regexp_Extract using Google Analytics url

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related