Hive query to extract part of the string using regexp

user_13226307 0
Viewed 289

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.

hiveql hive hadoop sql regex
2 Answers

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
user_27003440

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     |
+-----+--------+-------+--+
user_85714980
Related