BigQuery REGEXP_EXTRACT return null

Question

from google analytics export I am trying to extract last number from the trafficSource.campaign field with next query:

SELECT
 REGEXP_EXTRACT(trafficSource.campaign,r':(\d+$)') as campaign,
FROM
  [95677969.ga_sessions_20160109] AS results,
WHERE
  hits.type IN ('TRANSACTION','PAGE')

This Query works fine in UI and the number is returned by every row match the reg expresion, but when I copy paste this query in my script allways null value is returned.

trafficSource.campaign values are like this:

_dfa_107202:4637224:8531522

Please can anybody help me?

Thank you for your comment @VadimSolovey, the problem was with the regex engin, it does´nt accept re2 perl character classes. — Ricardo Bruzos
– Ricardo Bruzos, Commented Feb 10, 2016 at 16:53

Felipe Hoffa · Accepted Answer · 2016-02-09 17:39:13Z

3

Escaping is the problem here.

When you give BigQuery something like:

SELECT COUNT(*) FROM [publicdata:samples.shakespeare] WHERE REGEXP_MATCH(word, r'^\w$')

That will work fine in the BigQuery UI (283 is the result). But if you copy the same string into your favorite programming language, it will probably try to read the \ as one of its escape characters, so you will have to double escape it - or figure an alternative way to feed it the string unaltered by your script interpreter.

answered Feb 9, 2016 at 17:39

Felipe Hoffa

59.8k23 gold badges185 silver badges365 bronze badges

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

1 Comment

Ricardo Bruzos Over a year ago

Hi Felipe, you are right, the problem is in the regex engine and it works with r'([[:digit:]]{3,}$)'

Collectives™ on Stack Overflow

BigQuery REGEXP_EXTRACT return null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related