Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How can I remove accents from a string in Impala?

Highlighted

How can I remove accents from a string in Impala?

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

Re: How can I remove accents from a string in Impala?

Cloudera Employee
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

Re: How can I remove accents from a string in Impala?

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. 

Re: How can I remove accents from a string in Impala?

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

-Andrew
Don't have an account?
Coming from Hortonworks? Activate your account here