[SOLVED] snowflake stored procedure with special characters not working

snowflake stored procedure with special characters not working

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.

Solution

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