CODEWITHSUNDEEP
asciisnowflake-cloud-data-platformnon-ascii-characters
QMan5
QMan5

Reputation: 779

Replacing non-ascii or non-english characters to ascii or english characters within a SELECT Statement in Snowflake

I am trying to replace non-english/ascii characters with their English/ascii counterpart in Snowflake. I have figured out a way to do it in Snowflake.

Here's my current solution:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(columnname,'é','e'),'ê','e'),'ë','e'),'è','e'),'É','E'),'È','E'),'Ê','E'),'Ë','E'),'ð','D'),'Ð','D'),'â','a'),'à','a'),'á','a'),'ã','a'),'æ','a'),'à','a'),'å','a'),'Å','A'),'À','A'),'Á','A'),'Â','A'),'Ã','A'),'Æ','A'),'ä','a'),'Ä','A'),'ï','i'),'î','i'),'ì','i'),'í','i'),'Ì','I'),'Í','I'),'Î','I'),'Ï','I'),'ô','o'),'ò','o'),'ó','o'),'õ','o'),'ø','o'),'Ò','O'),'Ó','O'),'Ô','O'),'Õ','O'),'Ø','O'),'ö','o'),'Ö','O'),'û','u'),'ù','u'),'ú','u'),'Ù','U'),'Ú','U'),'Û','U'),'Ü','U'),'ü','u'),'ñ','n'),'Ñ','N'),'Ç','C'),'ç','c'),'ý','y'),'ÿ','y'),'Ý','Y'),'þ','T'),'Þ','t'),'ß','ss') replacement
FROM mytable;

As you can see, it's quite rudimentary and hard to read based on the amount of replace statements. Is there a more efficient way to do this in Snowflake? Please note, I don't have permission to insert/replace in the table itself. I need to do this within a SELECT statement using MySQL in Snowflake.

Upvotes: 2

Views: 1530

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

If you want a shorter alternative to so many replaces, without using an UDF, then you have TRANSLATE() in Snowflake:

select translate('Whát Úñé', 'áéñÚ', 'aenU');

-- What Une

Otherwise, this UDF is a great solution https://stackoverflow.com/a/69032269/5070879

Upvotes: 4

Related Questions