Reputation: 23
HIVE :- I have a column changeContext==>"A345|Fq*A|2017-05-01|2017-05-01" (string) out of which I need to extract A345 as another column. Any suggestion ? P.S. I have tried regexp_extract (running into vertex failure) so any other solution would be perfect.
Upvotes: 0
Views: 3656
Reputation: 44921
with t as (select "A345|Fq*A|2017-05-01|2017-05-01" as changeContext)
select substring_index(changeContext,'|',1) option_1
,split(changeContext,'\\|')[0] option_2
,substr(changeContext,1,instr(changeContext,'|')-1) option_3
,regexp_extract(changeContext,'[^|]*',0) option_4
,regexp_replace(changeContext,'\\|.*','') option_5
from t
+----------+----------+----------+----------+----------+
| option_1 | option_2 | option_3 | option_4 | option_5 |
+----------+----------+----------+----------+----------+
| A345 | A345 | A345 | A345 | A345 |
+----------+----------+----------+----------+----------+
Upvotes: 3