I have HIVE table with string column, in one column it have numbers of values and I would like to split those values into columns. Here is sample column values.
End result should look like this
I have used split function, since values in columns is not at same location , I am getting wrong values in columns, is there any function which takes value base on its tag value
You need to use regexp_extract function and keep matching regular expression and extract the value.
Regular expression:
lanes=>"(.*?)" //literal match for lanes=>" and capture until next following occurance "
and keep it in first capture group
Example:
with cte as (--sample data
select stack(2,string('lanes=>"2","txt_mid"=>"0"'),
string('"is_in"=>"parksville"'))as(c1))
select regexp_extract(c1,'lanes=>"(.*?)"',1)lanes,
regexp_extract(c1,'"txt_mid"=>"(.*?)"',1)txt_mid
from cte;
Result:
lanes txt_mid
2 0
In case if you want to null values for missing data records, then use case-when-then statement to check length of the field if 0 then have null value for the record.
