# JavaScript UDF not working as macro

**URL:** <https://discourse.getdbt.com/t/javascript-udf-not-working-as-macro/9286>\
**Category:** Help\
**Tags:** snowflake, hooks\
**Created:** [July 25, 2023, 7:40pm UTC](https://discourse.getdbt.com/t/javascript-udf-not-working-as-macro/9286 "2023-07-25T19:40:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![scroobiustrip](https://avatars.discourse-cdn.com/v4/letter/s/3be4f8/32.png) [@scroobiustrip](https://discourse.getdbt.com/u/scroobiustrip)\
**Post date:** [July 25, 2023, 7:40pm UTC](https://discourse.getdbt.com/t/javascript-udf-not-working-as-macro/9286/1 "2023-07-25T19:40:30Z")

</div>

Hey, I’m trying to implement some of the more advanced regexp commands outlined in these snowflake UDFs:

- [Snowflake UDFs - Regular Expressions](https://github.com/sfc-gh-gpavlik/SnowflakeUDFs/blob/main/RegularExpressions/regexp2.sql)

However, while these functions work fine locally + in production (without dbt), I am unable to transfer them successfully to be used as macros in my dbt project.

The methodology I’m using to port some of this code over to dbt is this one:

- [Snowflake JavaScript UDF - Unit test and deploy with DBT](https://medium.com/@hamletmaschine666/snowflake-javascript-udf-unit-test-and-deploy-with-dbt-4996826da707)

Where the author recommends to implement a post hook where you create the UDF:

- `+post-hook: "{{ create_regexp_subtr2_udf() }}"`

… which allows dbt to pick up the function reference correctly.

However, when I come to test the model in which the macro is being used, I encounter the following error in JavaScript which I don’t encounter when running as a normal UDF either locally or in Airflow:

```auto
100132 (P0000): JavaScript execution error: Uncaught TypeError: Cannot read properties of null (reading 'search') in REGEXP_SUBSTR2 at 
'instr = str.search(regex);' position 24
18:58:00 stackstrace: 
18:58:00 REGEXP_SUBSTR2 line: 16

```

If anyone has any ideas as to why the same code in snowflake is not making the transition to dbt would be a massive help! 😄

> **Full code**
>
> **regexp\_substr2\_js.sql**
> 
> ```javascript
> {% macro regexp_substr2_js() %}
> $$
> if (OCCURRENCE < 1) OCCURRENCE = 1;
> 
> var pos = POSITION - 1;
> if (pos < 0) pos = 0;
> var str = SUBJECT;
> if (pos != 0) {
> str = str.substring(pos);
> }
> var params = "g" + PARAMETERS;
> var regex = new RegExp(PATTERN, params)
> var instr = 0;
> var cursor = pos;
> 
> for (i = 1; i <= OCCURRENCE; i++) {
> /* Bit that's failing... */
> instr = str.search(regex);
> /* End. */
> if (instr === -1) break;
> str = str.substring(instr);
> cursor = cursor + instr + 1;
> }
> 
> if (instr != -1) {
> return str.match(regex);
> } else {
> return "";
> }
> $$
> {% endmacro %}
> 
> ```
> 
> **create\_regexp\_substr2\_udf.sql**
> 
> ```sql
> {% macro create_regexp_substr2_udf() %}
> 
> CREATE OR REPLACE FUNCTION REGEXP_SUBSTR2(SUBJECT string, PATTERN string, POSITION float, OCCURRENCE float, PARAMETERS string, GROUP_NUM float)
> RETURNS string
> LANGUAGE JAVASCRIPT STRICT IMMUTABLE
> AS '{{ regexp_substr2_js() }}';
> 
> {% endmacro %}
> 
> ```
> 
> **query.sql**
> 
> - This is querying a table which contains a column of URL subfolders joined together with `LISTAGG` to create a regex to extract URL subfolders of interest.
> 
> ```sql
> WITH 
> subfolders AS (
> select listagg(pattern, '|') as folder 
> from {{ ref('lookup_urls') }}    
> )
> 
> SELECT
> URL,
> {{ target.schema }}.regexp_substr2(URL, (select folder from subfolders), 1, 1, 'i')
> FROM {{ ref('all_urls' )}}
> 
> ```

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [July 30, 2023, 3:55am UTC](https://discourse.getdbt.com/t/javascript-udf-not-working-as-macro/9286/2 "2023-07-30T03:55:12Z")

</div>

can you please post the compiled sql of this model