CODEWITHSUNDEEP
google-bigquery
sam
sam

Reputation: 407

how to extract string using Bigquery regexp_extract

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

Upvotes: 1

Views: 5184

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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

Upvotes: 1

Related Questions