Skip to content
Advertisement

Hive regexp_extract numeric value from a string

I have a table as:

column1                         
A.A=123; B.B=124; C.C=125         
C.C=127     

I am trying to get the numeric values from the table. The expected output is

A -> 123 / B -> 124 etc

I am trying to do using regexp_extract

Any suggestions please?

Advertisement

Answer

If the delimiters are fixed – ‘; ‘ between key-value pairs and ‘=’ between key and value, you can use str_to_map function:

select str_to_map('A.A=123; B.B=124; C.C=125','; ','=')['A.A'] as A --returns 123

If you prefer regexp:

select 
regexp_extract('A.A=123; B.B=124; C.C=125','A.A=(\d*)',1) as A, --returns 123
regexp_extract('A.A=123; B.B=124; C.C=125','B.B=(\d*)',1) as B --returns 124

and so on

for case insensitive add (?i) to the regexp

select regexp_extract('A.A=123; b.b=124; C.C=125','(?i)B.B=(\d*)',1) as B --returns 124
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement