Snowflake removing backslashes during Procedure compilation?-CodePudding

For some reason Snowflake is removing backslashes from my regex function, but only when I put the function inbetween the "$$" when creating a Javascript Procedure.

For context here is my Regex Function:

CREATE OR REPLACE FUNCTION "REGEX_REPLACE_ME"("SUBJECT" VARCHAR(16777216), "PATTERN" VARCHAR(16777216), "REPLACEMENT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
AS '
  
    const p = SUBJECT;
    let regex = new RegExp(PATTERN, ''i'') 
    return p.replace(regex, REPLACEMENT);
  ';

When I run it simply in SQL, it works; it will change "APPLE.com" to "APPLE".

SELECT REXP_REPLACE_ME('APPLE.COM','\\.[A-Z]{2,3}',' ') -- the regex pattern \\.[A-Z]{2,3} is meant to remove domains i.e. ".com",".org", etc..

However, when I run it within $$ of a Store Procedure it removes the backslashes from my regex pattern and consequently changing the regex pattern completely.

My regex changes from \\.[A-Z]{2,3} to -> .[A-Z]{2,3}

CREATE or replace PROCEDURE TESTING_FUNC_1_THIS_CAN_BE_DELETED()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$

var rs = snowflake.execute( { sqlText:
`
    CREATE OR REPLACE VIEW Database.Schema.Table AS

    SELECT REXP_REPLACE_ME('APPLE.COM','\\.[A-Z]{2,3}',' ')  as column_cleaned
 --   ,REXP_REPLACE_ME_WTF('APPLE.COM','\\.[A-Z]{2,3}',' ') AS WHAT_PATTERN_IS_BEING_OUTPUTTED -- function logic in code block below


    
`

                            } );


  $$;

call TESTING_FUNC_1_THIS_CAN_BE_DELETED();
    select * from Database.Schema.Table

Scratching my head here, I created this function to show what pattern it was outputting and this is how I came to the conclusion (I can be wrong here..) that when Snowflake is compiling it's removing the backslash...

CREATE OR REPLACE FUNCTION "REXP_REPLACE_ME_WTF"("SUBJECT" VARCHAR(16777216), "PATTERN" VARCHAR(16777216), "REPLACEMENT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
AS '
  
    const p = SUBJECT;
    let regex = new RegExp(PATTERN, ''i'');
    //return p.replace(regex, REPLACEMENT);
    return PATTERN;
  ';

Any ideas?

CodePudding user response：

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

JavaScript also uses \ as an escape character, so to represent a backslash in JavaScript you also have to use \\

If you want to represent a single backslash in Snowflake SQL through JavaScript, you have to send 2 x 2 = 4 backslashes \\\\

CodePudding user response：

Probably something to do with escape characters etc

Try wrapping your SQL statement in backticks instead of single quotes: ``