snowflake stored procedure with special characters not working

Question

I want to identify the phone number is in correct format or not. e.g. format. (XXX) XXX-XXXX

Here is the SQL that is working fine

select RLIKE(  '(800) 456-7891', '\\([0-9]{3}\\) [0-9]{3}-[0-9]{4}');

But when tried to replicate this function inside a stored procedure, I am not able to get the desired results.

Here is stored procedure code:

CREATE OR REPLACE PROCEDURE "SP_TEST"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
var create_cmd_0 = `
CREATE OR REPLACE TABLE  QA_TEST_SP_DEBUG
AS 
select '(800) 456-7891' AS PHONE_NUMBER,  RLIKE('(800) 456-7891','\\([0-9]{3}\\) [0-9]{3}-[0-9]{4}') AS FLAG   
;`
var sql_create_0 = snowflake.createStatement({sqlText: create_cmd_0});
var create_result_0 = sql_create_0.execute();   
return 'SUCCESS';
  $$
  ;

Once the stored procedure is created, and executed. When I query the QA_TEST_SP_DEBUG, I see false for this record. Some how character '(' is being ignored. How do I make the stored procedure treat this as normal character?

I have added another backslash but still didn't work.

Appreciate your quick help on this.

Answer 1

When you wrap your SQL query to JavaScript it interprets the backslashes as escape characters. Therefore, this part \\([0-9]{3}\\is translated to \([0-9]{3}\which makes your regexp different.

To keep your regexp logic just add another pair of back slashes when you create the stored procedure, like:

select '(800) 456-7891' AS PHONE_NUMBER,  RLIKE('(800) 456-7891','\\\\([0-9]{3}\\\\) [0-9]{3}-[0-9]{4}') AS FLAG