Replace all instances in a string

Question

I have the below Snowflake Javascript Stored procedure where I need to replace multiple instances of a string in an sql statement.

 CREATE OR REPLACE PROCEDURE MYSCHEMA.CountryDelete(COUNTRY VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var error_msg = 'Success';      
        try 
        {
            var sql_cmd_temp = `DELETE FROM MYSCHEMA.CountryTable_{country} 
            WHERE COUNTRY_CODE = '{country}';`  
            var sql_cmd = sql_cmd_temp.replace("{country}", COUNTRY);       
            return sql_cmd;   
        }
        catch(err) 
        {
            error_msg += "ERROR : " + err.message;
        }
        return sql_cmd;     
    $$;

When I execute the above Stored procedure am getting the below output. Here, 1st instance of the {country} is getting replaced with CH (where CH is the parameter value that I am sending while executing this Stored procedure) but not the 2nd one.

DELETE FROM ODP_SUBNATIONAL_STAGING.IDP_SUBNATIONAL_SALES_CH 
        WHERE COUNTRY_CODE = '{country}';

I am expecting the below output.

DELETE FROM ODP_SUBNATIONAL_STAGING.IDP_SUBNATIONAL_SALES_CH 
            WHERE COUNTRY_CODE = 'CH';

I also tried Javascript's replaceAll instead of replace but it is returning [NULL].

Please note that since I have to hardcode the sql in multiple lines, I need to use the Grave accent symbol while storing sql string into the variable. Otherwise I would have used the " symbol to store the sql and use + symbol to make it dynamic.

It will be really thankful if someone can help in sorting this issue out.

Answer 1

Finally, I found a solution for this using the constructor function of the RegExp object.

var sql_cmd = sql_cmd_temp.replace(new RegExp('{country}', 'g'), COUNTRY);

Here's is the updated code.

CREATE OR REPLACE PROCEDURE MYSCHEMA.CountryDelete(COUNTRY VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var error_msg = 'Success';      
        try 
        {
            var sql_cmd_temp = `DELETE FROM MYSCHEMA.CountryTable_{country} 
            WHERE COUNTRY_CODE = '{country}';`  
            var sql_cmd = sql_cmd_temp.replace(new RegExp('{country}', 'g'), COUNTRY);       
            return sql_cmd;   
        }
        catch(err) 
        {
            error_msg += "ERROR : " + err.message;
        }
        return sql_cmd;     
    $$;

And here's the result.

DELETE FROM ODP_SUBNATIONAL_STAGING.IDP_SUBNATIONAL_SALES_CH 
            WHERE COUNTRY_CODE = 'CH';

Answer 2

You can use JavaScript template literals ${myVariable}; then you don't have to do a replace on each variable used as a template in a string. This works as long as you terminate your string literal with backticks.

CREATE OR REPLACE PROCEDURE CountryDelete(COUNTRY VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var error_msg = 'Success';      
        try 
        {
            var sql_cmd = `DELETE FROM MYSCHEMA.CountryTable_${COUNTRY} 
            WHERE COUNTRY_CODE = '${COUNTRY}';`      
            return sql_cmd;   
        }
        catch(err) 
        {
            error_msg += "ERROR : " + err.message;
        }
        return sql_cmd;     
    $$;

Answer 3

A safer way would be to use binds - if your plan is to execute the query within the stored procedure:

create or replace temp table prefix_delete_me as 
select 1 x, 'me' as country;

CREATE OR REPLACE PROCEDURE testbind(COUNTRY VARCHAR) 
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$

var stmt = snowflake.createStatement({
    sqlText: `
        select *
        from identifier(:2)
        where country=:1
        and country != concat('hello', :1, :1)
    `
   , binds:[COUNTRY, 'prefix_delete_'+COUNTRY]
});

// execute the statement to prove its correct
stmt.execute(); 

// just get the sql statement
return stmt.getSqlText();
$$;
    
call testbind('me')