snowflake string replace javascript udf

0
votes

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?

2
javascriptsnowflake-cloud-data-platform

2 Answers

2
votes

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?'
>
0
votes

Here is the working version for those who come across this in the future:

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