I was able to write a name cleaning logic in a JavaScript udtf in snowflake as the code was very repetitive for me and it worked fine.
It would first replace non alphanumeric characters with whitespace then split the name on whitespace, if the words repeated in the name, it would keep the first occurence, then joined it back as cleaned name.
I thought of using it inside a dbt macro so that my other dbt models can use it but it was throwing error like unknown function, etc. Is there a way I can refer the already created function? Or what can I use to generate the same functionality.
PS: I also tried creating macro using SQL code including multiple cte, lateral flatten, split and listagg, again this worked in snowflake but not inside macro.