how to extract string using Bigquery regexp_extract

Question

text that I am trying to parse

Text: sometext herex0D Name: Davidx0D some more text: x0D - value1x0D - google maps to find x0D x0D What you did: x0D - i did some stuff x0D

The text I want to extract is anything after

What you did:

So the result should be

x0D - i did some stuff x0D

This is what I have but it is not returning what I want.

select REGEXP_EXTRACT('Text: sometext herex0D Name: Davidx0D some more text: x0D - value1x0D - google maps to find x0D x0D What you did: x0D - i did some stuff x0D', r"What you did:(.*)")

But, if you run this as-is it will return the right result because I pasted the string in query. But if I replace the string with the column name in the table then it doesn't work. I only get

x0D

back

works for me with column. something with the data itself - can you compare length of typed string vs. that string in table? — Mikhail Berlyant
– Mikhail Berlyant, Commented Sep 14, 2018 at 18:16
Length is different by 2. Length by using column name comes out to 2 more. — sam
– sam, Commented Sep 14, 2018 at 18:48
so this would be a key for addressing the issue I think. can you determine what those are? this would explain effect you see — Mikhail Berlyant
– Mikhail Berlyant, Commented Sep 14, 2018 at 18:53

Mikhail Berlyant · Accepted Answer · 2018-09-14 19:53:47Z

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT ''''Text: sometext herex0D Name: Davidx0D some more text: x0D - value1x0D - google maps to find x0D x0D What you did: x0D
  i did some stuff x0D''' str
)
SELECT 
  REGEXP_EXTRACT(str, r"What you did:(.*)") result,
  SUBSTR(str, STRPOS(str, "What you did:") + LENGTH("What you did:")) adjusted_result
FROM `project.dataset.table`

Row     result      adjusted_result  
1       x0D         x0D i did some stuff x0D

Update

So, in your table you have strings with new line that needs to be considered during the regexp parsing

By default . does not match \n
To make it work you need to use flag s like below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT ''''Text: sometext herex0D Name: Davidx0D some more text: x0D - value1x0D - google maps to find x0D x0D What you did: x0D
  i did some stuff x0D''' str
)
SELECT 
  REGEXP_EXTRACT(str, r"What you did:(.*)") result,
  REGEXP_EXTRACT(str, r"(?s)What you did:(.*)") multiline_result
FROM `project.dataset.table`

with result

Row     result      multiline_result     
1       x0D         x0D i did some stuff x0D

Collectives™ on Stack Overflow

how to extract string using Bigquery regexp_extract

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related