CODEWITHSUNDEEP
user-defined-functionssnowflake-cloud-data-platform
Marshall Thomson
Marshall Thomson

Reputation: 21

js replace function not working within Snowflake UDF (but works fine in js previewer)

I'm trying create a Snowflake UDF to remove all non-numerics from a number string, like this:

create or replace function fnValidAusMobile(STRIN string)
  returns string
  language javascript
  strict
  as
  '
  var strIn = STRIN.replace(/\D/g,"") 
  return strIn
  '
  ;

However, when I test it, I can see the regex is not working, because the below returns this: '+61 401 111-111'

SELECT fnValidAusMobile('+61 401 111-111')

Strangely, when I use a js previewer, the regex works fine, so I'm baffled.

Any clues? Are there different rules / syntax for regex when used within a Snowflake UDF?

TIA.

Upvotes: 1

Views: 578

Answers (2)

Marcel
Marcel

Reputation: 2612

The problem is about escaping. If you change the ' (single quote) to '$$' (double dollar sign) it should work fine.

create or replace function fnValidAusMobile(STRIN string)
  returns string
  language javascript
  strict
  as
  $$
  var strIn = STRIN.replace(/\D/g,"") 
  return strIn
  $$
  ;

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

Could you write the regexp with double slashes?

create or replace function fnValidAusMobile(STRIN string)
  returns string
  language javascript
  strict
  as
  '
  var strIn = STRIN.replace(/\\D/g,"") 
  return strIn
  '
  ;

Upvotes: 1

Related Questions