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 store procedure, i am not able to get the desired results

Here is stored proc 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 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 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