Assume I have the following string: "Gánémílózú"
I need the query to return "Ganemilozu" (without the accents)
I've tried to use the translate function, as follows:
SELECT translate('Gánémílózú', 'áéíóú', 'aeiou') FROM (SELECT 1) as dual;
But it returns "Gaenaomalaza". Apparently I didn't understand the translate function.
Can anyone explain why doesn't my approach work or lead me to a possible solution to remove the accents?
Thanks in advance!
I found that the problem lies not in the translate function but in the handling of accents by Impala.
SELECT translate('Gánémílózú', 'Gnmlz', '12345') FROM (SELECT 1) as dual;
Works perfectly fine by returning 1á2é3í4ó5ú
Does anyone know how does Impala handle characters with accents?
Thanks for clarifying that translate only works on ASCII characters.
I found a similar solution to the one you proposed, using the replace function instead of the regexp replace. However, as you mention, the solution is really not scalable and it is very tedious to implement and maintain.