How to extract a specific string from a key,value pair data in Big Query using regexp_extract?

Question

I am trying to extract the value for a particular key 'xyz' and tried the below regex expression to accomplish it. Is this an effective method to deal with key value pairs? Can someone suggest me if there is any other solution which is more effective. Thank you.

**

 - Input & SQL :-



**

 SELECT FIRST( SPLIT( regexp_extract(kvp,r'SuppressFlexCacheHydrationIndicator=(.*)&'), '&' ) ) AS SuppressFlexCacheHydrationIndicator,
       regexp_extract(kvp,r'campaignName=(.*)$') AS campaign,
       regexp_extract(LOWER(kvp),r'resultcode=(.*)&') AS resultcode,       
  FROM ( SELECT 'SuppressFlexCacheHydrationIndicator=True&templateVersionId=5&vmtaText=mail2&sequenceId=300&resultCode=DoNotMailBounceList&campaignName=classicimport' AS kvp )

Output :-

    SuppressFlexCacheHydrationIndicator campaign        resultcode   
    True                                classicimport   donotmailbouncelist

Comments :-

In the above SQL I am using FIRST(SPLIT()) to extract the sub-string from the input which I think that it's not effective and can be implemented just by using regular expression.Please kindly share your insights if there is any other possible solution. Thanks.

Mosha Pasumansky · Accepted Answer · 2015-10-13 20:44:33Z

1

I would use slightly different regular expressions to avoid SPLIT and FIRST costs, as well as encoding special knowledge about parameter positions in URL:

SELECT regexp_extract(kvp,r'SuppressFlexCacheHydrationIndicator=([^&]*)') AS SuppressFlexCacheHydrationIndicator,
       regexp_extract(kvp,r'campaignName=([^&]*)') AS campaign,
       regexp_extract(LOWER(kvp),r'resultcode=([^&]*)') AS resultcode,       
  FROM ( SELECT 'SuppressFlexCacheHydrationIndicator=True&templateVersionId=5&vmtaText=mail2&sequenceId=300&resultCode=DoNotMailBounceList&campaignName=classicimport' AS kvp )

answered Oct 13, 2015 at 20:44

Mosha Pasumansky

14.1k5 gold badges36 silver badges57 bronze badges

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

3 Comments

Teja Over a year ago

Thanks Mohsa. It works fine. Can you explain me the usage of [^&]*. By looking at the result I understand that we are looking for the first ampersand that follows the key. Is this right interpretation?

Mosha Pasumansky Over a year ago

The [^&] means any character except &, so the regular expression match will stop when it sees & in the input.

Teja Over a year ago

Thank you. Just wondering if you know/have any good documentation(or suggestions) that has more details about regex in BQ. I have checked the native BQ documentation for regex but its very limited.

Collectives™ on Stack Overflow

How to extract a specific string from a key,value pair data in Big Query using regexp_extract?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related