Remove Unicode characters while querying in Hive

user_5059335 0
Viewed 13840

I want to clean the unicode the data from the Hive table. The following is the data,

select ('http://10.0.0.1/���m��v������)�a�^�����kn:4�+9x�2c��m�{��')

My required output is to find if there are any unicode characters in my column and to remove it. The output here should be,

http://10.0.0.1/

or completely null. Either of them is fine. If a row contains any unicode character, it is fine to make it null completely.

The following are my tryings,

 select REGEXP_REPLACE('http://10.0.0.1/���m��v������)�a�^�����kn:4�+9x�2c��m�{��', '\\[[:xdigit:]]{4}', '')

and

 select REGEXP_REPLACE('http://10.0.0.1/���m��v������)�a�^�����kn:4�+9x�2c��m�{��', '[||chr(128)||'-'||chr(255)||]', '')

Executed as Single statement.  Failed [40000 : 42000] Error while compiling statement: FAILED: ParseException line 1:193 mismatched input '<EOF>' expecting ) near ')' in function specification 
Elapsed time = 00:00:00.220 
 
STATEMENT 1: SELECT Statement failed.

Can somebody help me in cleaning these in my table?

Places where is is working,

select REGEXP_REPLACE('"http://r.rxthdr.com/w?i=s�F�""�HY|�K�>�0����D����W8뤒�O0�Q�D�1��Vc~�j[Q��f��{u�Be�S>n���Ò���&��F9���C�i��8:ڔ�_@ĪO��K?�Ēc�6��=��v[�����D�$%��:�a�40ݩ�&O��K��""�0�a<x��TcX���b��TN�}�x�o��UY$K�I�Օ""��(+�M���E�=K�A�I�A���q#l�(�yt�5��h}��~[��YOA��G�=ïˆï¿½{���. �Q���ح;x=�s�0:�', '(?s).*\\P{ASCII}.*', '')

Places where it is not working,

 select REGEXP_REPLACE('c4k0j,}W""d+2|4y0hkCkRh+.{pq80{?X8O>b<:ph.3!{T', '(?s).*\\P{ASCII}.*', '')

 select REGEXP_REPLACE('z|""},}69]6N2|c_;5.su={IU+|8ubq1<r$!Xxy#?Bhkv20:jXNgRh+5fwj:ndfWBJ}e)>','(?s).*\\P{ASCII}.*', '')

The first one in the image has a unicode character. But while pasting it becomes a dot.

enter image description here

hive hadoop unicode sql regex
2 Answers

If you are handling URLs you may want to consider removing characters that are considered unsafe in context of URLs. Broadly speaking safe URL characters are:

Standard (safe) characters:

0 1 2 3 4 5 6 7 8 9
a b c d e f g h I j k l m n o p q r s t u v w x y z
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Special characters:

$ – _ . + ! * ‘ ( ) ,

original article

In that situation, a more natural choice for me is using regexp_extract in the following manner:

regexp_extract(question,'[a-zA-Z0-9\$\–\_\.\+\!\*\‘\(\)]*', 0)
user_16555670
Related