Big query: Replace string parts in multiple columns dynamically

Question

I have a dynamic table with an indertermined number of columns [statement_nn] with strings. I need to replace string parts by the correspondent item

The table looks like this: Original table

How could I do the operation having in mind that “statement” columns are variable and cannot specifically use them in a standard REPLACE statement?

This is the end result Im looking to get: Desired result

I tried to make an array of "statement" columns and replace items there, but I need to be able to keep column names to get the desired result

Chris · Accepted Answer · 2022-06-19 16:18:41Z

1

You can use EXECUTE IMMEDIATE to dynamically select and replace your desired items. The way I solved it was replacing each placeholder manually with the following query:

execute immediate (
select 'select *  replace(' || 
  string_agg('regexp_replace(' || 
    'regexp_replace(' || 
      'regexp_replace(' || column_name || ',' || ' r"<name>", name)' 
      || ',' || ' r"<surname>", surname)' 
      || ',' || ' r"<registration_year>", CAST(registration_year as STRING))' 
      || ' as ' || column_name, ', ') || 
') from project_name.dataset_name.table_name'
from `project_name.dataset_name.INFORMATION_SCHEMA.COLUMNS`
where table_name = 'table_name'
and STARTS_WITH(column_name, "statement_")
)

Remember to replace project_name, dataset_name, and table_name.

edited Jun 19, 2022 at 16:18

Chris

139k139 gold badges317 silver badges293 bronze badges

answered Jun 16, 2022 at 10:27

Dave Verano

113 bronze badges

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

Collectives™ on Stack Overflow

Big query: Replace string parts in multiple columns dynamically

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