REGEXP_SUBSTR function in stored procedure returns null

user_14400998 0
Viewed 339

I have the following string: 'AAA|BBB||CCC|1.23'

I would like to return: 'CCC|1.23'

When using the regexp: \w+\|\d(.\d+|$) I am able to get the desired results.

When in Snowflake running the following query, returns the correct results:

SELECT REGEXP_SUBSTR('AAA|BBB||CCC|1.23', '\\w+\\|\\d(.\\d+|$)') AS regexp_return;

However when used in a stored procedure as follows:

CREATE OR REPLACE PROCEDURE dnr.regexp_issue ()
    returns string
    language javascript
    execute as owner
    
    AS
    
    $$
    var sql_statement = `SELECT REGEXP_SUBSTR('AAA|BBB||CCC|1.23', '\\w+\\|\\d(.\\d+|$)') AS regexp_return;`   
    
    var query = snowflake.createStatement({sqlText: sql_statement});
    var query_res = query.execute();
    query_res.next();
    result = query_res.getColumnValue(1);
    
    return result;
    
    $$;

The resulting CALL dnr.regexp_issue(); returns a NULL as if no matching pattern was found.

Any ideas?

snowflake-cloud-data-platform
2 Answers

the slashed need to be double double quoted as they are going through two string parsers.

CREATE OR REPLACE PROCEDURE regexp_issue ()
    returns string
    language javascript
    execute as owner
    
    AS
    
    $$
    var sql_statement = `SELECT REGEXP_SUBSTR('AAA|BBB||CCC|1.23', '\\\\w+\\\\|\\\\d(.\\\\d+|$)') AS regexp_return;`   
    
    var query = snowflake.createStatement({sqlText: sql_statement});
    var query_res = query.execute();
    query_res.next();
    result = query_res.getColumnValue(1);
    
    return result;
    
    $$;
    
call regexp_issue();

gives:

REGEXP_ISSUE
CCC|1.23
user_439920

To add to Simeon's answer, you can also use .replace(/\\/g, "\\\\") at the end of a string that with double backslashes for Snowflake. That avoids using quadruple backslashes JavaScript + Snowflake SQL escape characters. It can make for more legible strings. It would look like this:

 var sql_statement = `SELECT REGEXP_SUBSTR('AAA|BBB||CCC|1.23', '\\w+\\|\\d(.\\d+|$)') AS regexp_return;`.replace(/\\/g, "\\\\");

You can also put it in two separate lines for even more clarity and a few microseconds more processing time.

var sql_statement = `SELECT REGEXP_SUBSTR('AAA|BBB||CCC|1.23', '\\w+\\|\\d(.\\d+|$)') AS regexp_return;`

sql_statement = sql_statement.replace(/\\/g, "\\\\");
user_127563810
Related