Extracting data using regexp_extract in Google BigQuery

Question

I am trying to extract data from a column which has multiple characters and I am only interested in getting the specific string from the input string. My sample input and outputs are as below. How can I implement this using regexp_extract function.Can someone share their thoughts on this if you have worked on GBQ.Thanks.

**

SQL:-

**

   SELECT request.url AS url 
    FROM [xyz.abc]
    WHERE regexp_extract(input,r'he=(.{32})')

**

Input:-

**

http://mpp.xyz.com/conv/v=5;m=1;t=16901;ts=20150516234355;he=5e3152eafc50ed0346df7f10095d07c4;catname=Horoscope  
2   http://mpp.xyz.com/conv/v=5;m=1;t=16901;ts=20150516234335;he=5e3152eafc50ed0346df7f10095d07c4;catname=High+Speed+Internet

**

Output :-

** **

5e3152eafc50ed0346df7f10095d07c4
5e3152eafc50ed0346df7f10095d07c4

**

Pentium10 · Accepted Answer · 2015-05-19 18:46:18Z

2

It's very simple to do:

select regexp_extract(input,r'he=(.{32})');

or as example:

select regexp_extract('http://mpp.xyz.com/conv/v=5;m=1;t=16901;ts=20150516234355;he=5e3152eafc50ed0346df7f10095d07c4;catname=Horoscope',r'he=(.{32})')

answered May 19, 2015 at 18:46

Pentium10

209k124 gold badges435 silver badges513 bronze badges

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

5 Comments

Teja Over a year ago

Thanks for the response Pentium. But I get this error when I run the same. Argument type mismatch in function LOGICAL_AND: first argument is type bool, second argument is type string

Pentium10 Over a year ago

You should check your data types, these work with STRING columns. Share your query if you still have issue to be able to help.

Pentium10 Over a year ago

That's a confused SQL, you cannot use this way the REGEXP as where. SELECT regexp_extract(request.url,r'he=(.{32})') as output AS url FROM [xyz.abc]

Teja Over a year ago

My bad... I removed it from where clause and tried running keeping it in SELECT clause... but it is returning me NULLs.

Teja Over a year ago

What does .{32} mean?

Collectives™ on Stack Overflow

Extracting data using regexp_extract in Google BigQuery

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related