Need Hive Query using regexp_extract to extract a part of a Field (type String).
The value in it is colon-separated
Field String:
ID(1001):10|Value(1002):8|Name(xyz):7
Need to extract the value after :
>ID -> 10
>Value -> 8
>Name -> 7
Tried using regexp_extract and could not get the output.
Remove strings in parenthesis, convert to map and use map['key'] to get fields:
select m['ID'] as id, m['Value'] as value, m['Name'] as Name
from (select str_to_map(regexp_replace('ID(1001):10|Value(1002):8|Name(xyz):7','\\(.*?\\)',''),'\\|',':') as m
)s;
Result:
id value name
10 8 7
Using regexp only:
SELECT regexp_extract(t.col,'(?i)ID.*?:(\\d+)\\|',1) as ID,
regexp_extract(t.col,'(?i)Value.*?:(.?+)\\|',1) as Value,
regexp_extract(t.col,'(?i)Name.*?:(.?+)\\|*',1) as Name
FROM (SELECT 'ID(1001):10|Value(1002):8|Name(xyz):7' AS col) t;
Result:
id value name
10 8 7
if you could use SPLIT function, it would be very easy. As an example:
SELECT split(split(t.col, '\\|')[0],'\\:')[1] AS ID,
split(split(t.col, '\\|')[1], '\\:')[1] AS Value,
split(split(t.col, '\\|')[2], '\\:')[1] AS Name
FROM (SELECT 'ID(1001):10|Value(1002):8|Name(xyz):7' AS col) t;
+-----+--------+-------+--+
| id | value | name |
+-----+--------+-------+--+
| 10 | 8 | 7 |
+-----+--------+-------+--+
regexp_extract