I have a declared variable in bigquery with words that I want to identify in a table
<code>DECLARE words_to_find DEFAULT r'(CASA|PERRO|CORAZON|GALLETA)';
WITH tabla AS
( select 1 as row_id, 'QUIERO IR CASA' as name union all
select 2 as row_id, 'PERRO BONITO' as name union all
select 3 as row_id, 'GRANDE CORAZON' as name union all
select 4 as row_id, 'LA GALLETA' as name
)
SELECT
*
FROM tabla
</code>
<code>DECLARE words_to_find DEFAULT r'(CASA|PERRO|CORAZON|GALLETA)';
WITH tabla AS
( select 1 as row_id, 'QUIERO IR CASA' as name union all
select 2 as row_id, 'PERRO BONITO' as name union all
select 3 as row_id, 'GRANDE CORAZON' as name union all
select 4 as row_id, 'LA GALLETA' as name
)
SELECT
*
FROM tabla
</code>
DECLARE words_to_find DEFAULT r'(CASA|PERRO|CORAZON|GALLETA)';
WITH tabla AS
( select 1 as row_id, 'QUIERO IR CASA' as name union all
select 2 as row_id, 'PERRO BONITO' as name union all
select 3 as row_id, 'GRANDE CORAZON' as name union all
select 4 as row_id, 'LA GALLETA' as name
)
SELECT
*
FROM tabla
| row_id | name |
|---|---|
| 1 | QUIERO IR CASA |
| 2 | PERRO BONITO |
| 3 | GRANDE CORAZON |
| 4 | LA GALLETA |
I would like to extract the words that match the declared variable from the column “name”, and save them in a new column, it would be something like this
| row_id | name | result |
|---|---|---|
| 1 | QUIERO IR CASA | CASA |
| 2 | PERRO BONITO. | PERRO |
| 3 | GRANDE CORAZON | CORAZON |
| 4 | LA GALLETA. | GALLETA |
Thanks for your help
How Can I achieve this? I try using REGEXP_EXTRACT but didn’t work.
1