Home > Back-end >  Hive regexp_extract numeric value from a string
Hive regexp_extract numeric value from a string

Time:11-19

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?

CodePudding user response:

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
  • Related