Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How can I remove accents from a string in Impala?

avatar
New Contributor

For example:

 

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!

 

EDIT:

 

I found that the problem lies not in the translate function but in the handling of accents by Impala.

 

The translation:

 

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?

 

3 REPLIES 3

avatar
Contributor
It looks like translate() only works with ascii characters.

You can do a replace a character at a time with something like:

SELECT regexp_replace(regexp_replace('Gánémílózú', 'á', 'a'), 'é', 'e');

but that is pretty ugly (I could only bear to do 2 characters).

-Andrew

avatar
New Contributor

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. 

avatar
Contributor
I created https://issues.apache.org/jira/browse/IMPALA-7511
to track this issue

-Andrew