how to pass in a column value in bigquery regexp_contains

Question

The following query does not work for containsy but does for containsx and containsz I want to be able to do search and replace with more flexibility with wildcards using word boundaries. I know I can do ' '||us||' ' as in REGEXP_CONTAINS((b.old_string),(' '||a.search||' ')) containsx . I'm not sure if this is the correct syntax r'(?i)(\b.'||a.search||'.\b)') I want to be able to replace us but not when it is part of another word.

WITH t1 AS (
    select 'us' search, 'united states' replacex
) ,
t2 as (
    select 'usa us bus' old_string
    )
 
select REGEXP_CONTAINS((b.old_string),(a.search)) containsx, --working 
REGEXP_CONTAINS((b.old_string),r'(?i)(\b.'||a.search||'.\b)') containsy, --not working
REGEXP_CONTAINS((b.old_string),r'(?i)(\b.us.\b)') containsz, --working
old_string,
replace (b.old_string,a.search,a.replacex) new_string,
search,
replacex
from t1 a
cross join t2 b
where REGEXP_CONTAINS((b.old_string),(a.search));

containsx	containsy	containsz	old_string	new_string	search	replacex
TRUE	FALSE	TRUE	usa us bus	united statesa united states bunited states	us	united states

EXPECTED OUTPUT

containsx	containsy	containsz	old_string	new_string	search	replacex
TRUE	FALSE	TRUE	usa us bus	usa united states bus	us	united states

Answer 1

WITH t1 AS (
    select 'us' search, 'united states' replacex
) ,
t2 as (
    select 'usa us bus' old_string union all  
    select 'I must go to the us' union all  
    select 'there is no country here' union all
    select 'I blew a fuse in the us'
    )
select 
REGEXP_CONTAINS((b.old_string),CONCAT(r'(?i)(\b', a.search, r'\b)')) is_contains, 
old_string,
regexp_replace (b.old_string, CONCAT(r'(?i)(\b', a.search, r'\b)'),a.replacex) new_string,
replacex
from t1 a
cross join t2 b
where REGEXP_CONTAINS((b.old_string),CONCAT(r'(?i)(\b', a.search, r'\b)'))  ;

is_contains	old_string	new_string	replacex
TRUE	usa us bus	usa united states bus	united states
TRUE	I must go to the us	I must go to the united states	united states
TRUE	I blew a fuse in the us	I blew a fuse in the united states	united states