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