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