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