In Hive I need to Get numeric value after a particular word is it possible?

Question

i want to get a numeric value immediately after a particular word in string In hive for example : APDSGDSCRAM051 in that i need to get numeric value after word RAM is it possible in hive

Note: its not a fixed length string

You can use regex to do this. I m not sure how to implement that in Hive, but here is sample for java which does the thing as you want. http://ideone.com/hdW7de. — Rajen Raiyarela, Dec 11 '15 at 06:48

score 1 · Answer 1 · answered Dec 11 '15 at 06:37

1

Here you go, you need to use substr and instr pre-defined hive functions:

create table str_testing (c string);
insert into table str_testing values ('APDSGDSCRAM051');
select substr(c, instr(c, 'RAM') + 3) from str_testing;
OK
051
Time taken: 0.243 seconds, Fetched: 1 row(s)

answered Dec 11 '15 at 06:37

Durga Viswanath Gadiraju

3,896
2
14
21

can we convert the resulted value to binary? – Hanmanth Dec 13 '15 at 11:37

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

As explained here, you can implemented in hive as

select regexp_extract(name, '\\d+', 0) from <table_name>;

Note: I do not have environment for Hive configured so you can check this by running at your end. Ya this will work only for first set of numbers found in your string, if you string has numbers at multiple places this might fail.

edited May 23 '17 at 10:27

Community

1
1

answered Dec 11 '15 at 06:53

Rajen Raiyarela

5,526
4
21
41

can we convert resulted value to binary ? – Hanmanth Dec 13 '15 at 06:48
that you can check ... there must be a way by using UDF if no inbuilt function available – Rajen Raiyarela Dec 14 '15 at 13:10

In Hive I need to Get numeric value after a particular word is it possible?

2 Answers2