How to Remove extra Backslash(/) in Snowflake using Stored Procedure?-CodePudding

using below select query i'm able to get the required result which removes extra slashes(3)

Answer:

select regexp_replace(regexp_replace('p\\\\','[\\|\\\\\|\\\|\\\\]','~'),'(~~|~)','\\\\');

while using the same query in StoredProcedure it throws an error:

Invalid regular expression: '[|\||\]', missing ]: [|\||\] At Statement.execute

Stored procedure code:

create or replace procedure back_slash(something varchar)
returns varchar
language javascript
execute as caller
as
$$
var command1=`select regexp_replace(regexp_replace('p\\\\','[\\|\\\\\|\\\|\\\\]','~'),'(~~|~)','\\\\')`;
var stmt1=snowflake.createStatement({sqlText: command1});
var rs1=stmt1.execute();
return 'successfully removed extra backslashs'
$$;

CodePudding user response：

So this:

select regexp_replace(regexp_replace('p\\\\','[\\|\\\\\|\\\|\\\\]','~'),'(~~|~)','\\\\');

returns:

p\

Change your stored procedure like this:

create or replace procedure back_slash(X1 varchar)
returns varchar
language javascript
execute as caller
as
$$
var command1="select regexp_replace(regexp_replace('p\\\\','[\\|\\\\\|\\\|\\\]','~'),'(~~|~)','\\\\')";
var stmt1=snowflake.createStatement({sqlText: command1});
var rs1=stmt1.execute();
rs1.next();
return rs1.getColumnValue(1);
$$;

and it will work. Observe I removed one \ before ].

call back_slash('x');

result:

p\

CodePudding user response：

Snowflake SQL uses backslash as an escape character. To represent a single backslash in Snowflake SQL, you have to use two \\.

JavaScript also uses backslash as an escape character. To represent a single backslash in Snowflake SQL through JavaScript, you have to use 2x2=4 backslashes \\\\

Refer to this question and answer:

Snowflake removing backslashes during Procedure compilation?

create or replace procedure back_slash(X1 varchar)
returns varchar
language javascript
execute as caller
as
$$
var command1="select regexp_replace(regexp_replace('p\\\\\\\\','[\\\\|\\\\\\\\\\|\\\\\\|\\\\\\\\]','~'),'(~~|~)','\\\\\\\\')";
var stmt1=snowflake.createStatement({sqlText: command1});
var rs1=stmt1.execute();
rs1.next();
return rs1.getColumnValue(1);
$$;

call back_slash('p\\\\');