Search code examples
sqlregexhivehiveqlregexp-replace

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?

Solution

  • 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