CODEWITHSUNDEEP
sqlregexperlgoogle-analytics
Andarin
Andarin

Reputation: 829

Extract substring from character A to character B or EOL using Regexp_extract of Big Query / Google Analytics

I'm working with Google Big Query and try to extract some information from a string column into another column using Regexp_extract. In short:

Data in myVariable:

yippie/eggs-spam/?portlet:hungry=1234
yippie/eggs-spam/?portlet:hungry=456&portlet:hungrier=7890

I want a column with:

1234
456

My command:

SELECT Regexp_extract(myVariable, r'SOME_MAGIC') as result
FROM table

I tried for SOME_MAGIC:

hungry=(.*)[&$] - null, 456 (I learned that $ is interpreted as is)
hungry=(.*)(&|$) - Error: Exactly one capturing group must be specified
hungry=(.*)^& - null, null
hungry=(&.*)?$ - null, null

I read this, but there the number has a fixed length. Also looked at this, but "?=" is no known command for perl.

Does anybody have an idea? Thank you in advance!

Upvotes: 2

Views: 1775

Answers (2)

Andrea
Andrea

Reputation: 21

I think I had a similar problem were I was trying to select the last 6 characters in a string (link_id) as a new column.

I kept getting this error:

Exactly one capturing group must be specified

My code originally was:

SELECT
...
REGEXP_EXTRACT(link_id, r'......$') AS updated_link_id
FROM sometable;

To get rid of the error and retrieve the correct substring as a column, I had to add parentheses around my regex string.

SELECT
...
REGEXP_EXTRACT(link_id, r'(......$)') AS updated_link_id
FROM sometable;

Upvotes: 2

Andarin
Andarin

Reputation: 829

I just found an answer to how I can solve my problem differently:

hungry=([0-9]+) - 1234, 456

It isn't an answer to my abstract question (regex for selecting Charater A to [Character B or EOL]), so it's not that satisfying. E.g. it won't work with

yippie/eggs-spam/?portlet:hungry=12AB34

However my original problem is solved. I leave the question open for a while in case somebody has a better answer.

Upvotes: 2

Related Questions