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.

Sarath Subramanian · Accepted Answer · 2021-05-28 15:22:08Z

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

Greg Pavlik · Accepted Answer · 2021-05-28 15:49:39Z

0

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;     
    $$;

answered May 28, 2021 at 15:49

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

1 Comment

Sarath Subramanian Over a year ago

While I compile this script (in DBVewer), it is asking for bind variable. I am also not able to override the country while I call this stored procedure. Is this how it work?

Felipe Hoffa · Accepted Answer · 2021-05-28 19:09:01Z

0

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')

answered May 28, 2021 at 19:09

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

2 Comments

Sarath Subramanian Over a year ago

This looks to be the better solution than my answer. BTW, is there any way that I can use CountryTable_:1 rather than using identifier(:2)? Because in the real scenario my sql will be very big and that I will have variety of table names inside the sql.

Felipe Hoffa Over a year ago

I tried different ways of encoding the table names, but this is the best I got for now

Collectives™ on Stack Overflow

Replace all instances in a string

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related