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.

Hive to extract multiple ip addresses from a string

Highlighted

Hive to extract multiple ip addresses from a string

Expert Contributor

Hi All,

I have a string

String:
some text with an ip 111.111.111.111 and a decimal 11.2323232 and some text here and then an int 1 and then some HTTP/1.1 with a 503 request and then another ip 222.222.222.222 and some imaginary 999.999.999.999

I want to output all the ip addresses in comma saperated. I tried the below

select regexp_replace(regexp_replace(String,'[^(\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})]',' '),'\\s+',',');
+------------------------------------------------------------------------+--+
|                                  _c0                                   |
+------------------------------------------------------------------------+--+
| ,111.111.111.111,11.2323232,1,1.1,503,222.222.222.222,999.999.999.999  |
+------------------------------------------------------------------------+--+

Expected output is : 111.111.111.111,222.222.222.222,999.999.999

Could you please help me

2 REPLIES 2

Re: Hive to extract multiple ip addresses from a string

Expert Contributor

@Shu can you please help me

Re: Hive to extract multiple ip addresses from a string

New Contributor

Are you familiar with user defined functions?

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