javascriptstored-proceduresreplacesnowflake-cloud-data-platformjava-stored-procedures

Replace all instances in a string


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.


Solution

  • 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';