Regexp_Replace using joining a table in snowflake

Question

I wanted to replace data in tableB with data in tableA. Table A:

Source_Col	Target_Col
DB_DEV	DB_UAT
CDB_DEV	CDB_UAT

I have another table which has one col with the value "Create or replace DB_DEV.SCH.VIEW AS SELECT * FROM CBD_DEV.SCH.TABLENAME".

I wanted to replace DB_DEV with DB_UAT and CBD_DEV with CBD_UAT querying from TableA. Is that possible?

Answer 1

-- Revised to use tables:

-- Create the source and target string patterns to replace

    create or replace table str_replace (source string, target string);
    insert into str_replace values ('DB_DEV','DB_UAT'),('CDB_DEV', 'CDB_UAT');
    
-- Create the table containing the original strings to be modified

    create or replace table orig_strings (query string, new_query string);
    insert into orig_strings values ('Create or replace view DB_DEV.SCH.V_TABLEA AS SELECT * FROM CDB_DEV.SCH.TABLENAME;',null),('Create or replace view DB_DEV.SCH.V_TABLEB AS SELECT * FROM CDB_DEV.SCH.TABLENAME;',null);
    

--- Query with row_number partition to pick 1st row from Cartesian join

select
    os.query,
    regexp_replace(os.query, str.source, str.target) as new_query
from
    orig_strings os,
    str_replace str QUALIFY row_number() over (
        partition by os.query
        order by
            os.query
    ) = 1;

-- Results

QUERY                                                    
                                                            NEW_QUERY
    Create or replace view DB_DEV.SCH.V_TABLEA AS SELECT * FROM CDB_DEV.SCH.TABLENAME;  Create or replace view DB_UAT.SCH.V_TABLEA AS SELECT * FROM CDB_UAT.SCH.TABLENAME;
    Create or replace view DB_DEV.SCH.V_TABLEB AS SELECT * FROM CDB_DEV.SCH.TABLENAME;  Create or replace view DB_UAT.SCH.V_TABLEB AS SELECT * FROM CDB_UAT.SCH.TABLENAME;

-- Update the table with the modified string

    update orig_strings os
    set os.new_query = regexp_replace(os.query,str.source,str.target) 
    from str_replace str;
    
    select * from orig_strings;

-- Results:

QUERY                                                    NEW_QUERY
Create or replace view DB_DEV.SCH.V_TABLEA AS SELECT * FROM CDB_DEV.SCH.TABLENAME;  Create or replace view DB_UAT.SCH.V_TABLEA AS SELECT * FROM CDB_UAT.SCH.TABLENAME;
Create or replace view DB_DEV.SCH.V_TABLEB AS SELECT * FROM CDB_DEV.SCH.TABLENAME;  Create or replace view DB_UAT.SCH.V_TABLEB AS SELECT * FROM CDB_UAT.SCH.TABLENAME;