How to replace character accented characters in Snowflake?

Question

I'm trying to replace accented characters from a column to "normal" characters.

select 'áááããã'

I'd like some operation which would return 'aaaaaa'.

Answer 1

Just found a solution with one of my colleagues.

select translate('áááããã','áéíóúãõâêôàç','aeiouaoaeoac')

We can also add a lower() to make it generalized for more cases

select translate(lower('ÁÁÁÃÃÃ'),'áéíóúãõâêôàç','aeiouaoaeoac')

Answer 2

There is a more general way that uses a built-in JavaScript function to replace them:

Remove Diacritics from string in Snowflake

create or replace function REPLACE_DIACRITICS("str" string)
returns string
language javascript
strict immutable
as
$$
    return str.normalize("NFD").replace(/\p{Diacritic}/gu, "");
$$;

select REPLACE_DIACRITICS('ö, é, č => a, o e, c');