For some reason Snowflake is removing backslashes from my regex function, but only when I put the function in between 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" 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. "","", etc.. 

However, when I run it within $$ of a stored procedure, it removes the backslashes from my regex pattern and consequently changing the regex pattern pletely.

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 piling 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?

For some reason Snowflake is removing backslashes from my regex function, but only when I put the function in between 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." 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. ".","", etc.. 

However, when I run it within $$ of a stored procedure, it removes the backslashes from my regex pattern and consequently changing the regex pattern pletely.

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 piling 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?

• javascript
• sql
• snowflake-cloud-data-platform

2 Answers 2

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 \\\\

Probably something to do with escape characters etc

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