Search code examples
regexgoogle-bigquerytext-extraction

REGEX certain rows of Data and keep the rest

I'm trying to extract the following data and convert it to a final column in BigQuery.

Raw Data SAY LOWERS = BAD.Q Virginia SAY LOWERS = BAD.U Oregon Georgia SAY LOWERS = BAD.U SAY LOWERS = BAD.A California

Final Version BAD.Q Virginia BAD.U Oregon Georgia BAD.U BAD.A California

Basically, I'm trying to remove "SAY LOWERS = " from all the data that has it, and keep everything after it, and keep everything that doesn't have that phrase.

Solution

  • This answer covers how to run regexp_replace in Google BigQuery, here is the query adapted for your use case:

    SELECT regexp_replace(your_column_name, r'SAY LOWERS = ', '') final_column_name
FROM your_table_name