CODEWITHSUNDEEP
regexgoogle-bigquery
Ricardo Bruzos
Ricardo Bruzos

Reputation: 23

BigQuery REGEXP_EXTRACT return null

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?

Upvotes: 2

Views: 970

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

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.

Upvotes: 3

Related Questions