Unsupported feature '|'. At Statement.execute error in snowflake regular expression

Question

create or replace procedure sp(path varchar)
returns varchar
language javascript
as
$$
var a="regexp_replace(PATH,'\\[\'\|\'\\]')";
var b="SELECT \n"+ a + " as path_name";
var c=snowflake.createStatement({sqlText:b});
var d=c.execute();
d.next();
return d.getColumnValue(1);
$$;
call sp('['Bank Country']');

This regular expression is working outside the procedure when I try to use it in the stored procedure it is displaying an error.

Execution error in store procedure SP: Unsupported feature '|'. A Statement. execute, line 6 position 8.

I am trying to do this with regular expression, if my string is in this format "['Bank Country']"--> "Bank Country" that means it has to strip off "['" and "']" in the string if "['" is at the first index position. If these special characters are present between the string, it has to do it in this way. For Example, "Customer['Bank Details']" --> "Customer.Bank Details" It has to strip off "['" and replace it with "." and remove the "']" in the string.

Answer 1

So given this is a carrying on of this question

You had valid SQL there.

select regexp_replace('[\'Customers NY\']','\\[\'|\'\\]','') as customername;

or

select translate('[\'Customers NY\']',$$[']$$,'');

So as Felipe notes: return early to check the SQL you have constructed:

create or replace procedure sp(path varchar)
returns varchar
language javascript
as
$$
    var a="regexp_replace(PATH,'\\[\'\|\'\\]')";
    var b="SELECT \n"+ a + " as path_name";
    return b;

    //var c=snowflake.createStatement({sqlText:b});
    //var d=c.execute();
    //d.next();
    //return d.getColumnValue(1);
$$;

and call it:

call sp('[\'Bank Country\']');

gives:

SELECT  regexp_replace(PATH,'\['|'\]') as path_name;

At this point there are two problems, you have not substituted PATH into your dynamic SQL. And you have lost some escaping.

I am assuming you are a student, so I will not give you a working solution, as that is your job to learn.

1. You current are substituting a into b, but PATH is not substituted into a

2. Your composed SQL is going to be parsed by the SQL parser, so needs to be valid SQL, thus the strings need to valid started/stopped, AND if you are going to use the same token like you have here it will need to be escaped, but the escapes also will need escaping to get past the parsing of the Stored Procedure.

So given I believe you are a student, and Felipie's "this can be done simpler out of a stored procedure" is correct it's simpler, if you have to do it "this way for the assignment", then the things you need to learn are the above points.

Answer 2

If you return b before createStatement, you will see exactly what kind of query this code is creating.

In this case it's SELECT regexp_replace(PATH,'\['|'\]') as path_name, which doesn't make sense.

Please start a new question explaining what's the goal of this function, sample inputs, sample desired outputs, and we can help with that.

In the meantime, a simple way to do what the last paragraph is asking for, without the need of an UDF:

select translate($$Customer['Bank Details']$$, $$[]'$$, '.')