snowflake string replace javascript udf



Trying to utilize Javascript’s String.prototype.replace() function in Snowflake, because apparently Snowflake’s regex_replace function lacks certain features such ast (forward/backward) look arounds.

Here is my Javascript UDF attempt:

CREATE OR REPLACE FUNCTION REXP_REPLACE_ME(subject TEXT, pattern TEXT, replacement TEXT)
  RETURNS string
  LANGUAGE JAVASCRIPT
  AS
  $$
    const p = SUBJECT;
    const regex = PATTERN;
    return p.replace(regex, REPLACEMENT);
  $$
  ;

However, when I try to execute using the example provided in the string.prototype.replace() documentation linked above. I’m not getting the expected result:

SELECT REXP_REPLACE_ME('The quick brown fox jumps over the lazy ferret. If the dog reacted, was it really lazy?', '/Dog/i', 'cat')

//expected output: 'The quick brown fox jumps over the lazy ferret. If the cat reacted, was it really lazy?'
//actual output: 'The quick brown fox jumps over the lazy ferret. If the dog reacted, was it really lazy?'

Any ideas?

Answer

because in Javascript the regex is not string lateral, it is it’s own thing.

> a = 'The quick brown fox jumps over the lazy ferret. If the dog reacted, was it really lazy?';
'The quick brown fox jumps over the lazy ferret. If the dog reacted, was it really lazy?'
> b = a.replace('/Dog/i', 'cat');
'The quick brown fox jumps over the lazy ferret. If the dog reacted, was it really lazy?'
> b = a.replace(/Dog/i, 'cat');
'The quick brown fox jumps over the lazy ferret. If the cat reacted, was it really lazy?'
> 


Source: stackoverflow