Hive extract numeric value from a string

Question

I have a table as:

column1                         column2 
The first value is 200 gb        need to restart (2 times)
The 2nd value is 700 gb          need (optional) to restart (10 times)

I am trying to get the numeric values from the table. The expected output is

column1_numeric      column2_numeric 
200                   2
700                   10

For column1: i trying to get data using: regexp_replace(column1, '[^0-9]', '') as column1_numeric; but this is not working for the 2nd row and returns 2700

For column2: I am trying as: regexp_replace(regexp_extract(column2,'\$(.*?)\$'), '[^0-9]', '') as column2_numeric but this is also not working for 2nd row and returns null values

Any suggestions please?

leftjoin · Accepted Answer · 2020-06-17 08:32:00Z

1

Extract last numeric value from the string '(\\d+)([^0-9]*)$':

select 
      regexp_extract(column1,'(\\d+)([^0-9]*)$',1) as column1_numeric,
      regexp_extract(column2,'(\\d+)([^0-9]*)$',1) as column2_numeric
   ...

It extracts

column1_numeric      column2_numeric 
200                   2
700                   10

Also instead of [^0-9] (not a digit) you can use \\D, which is a bit shorter:

'(\\d+)(\\D*)$'

edited Jun 17, 2020 at 8:32

answered Jun 17, 2020 at 8:02

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tanvir Over a year ago

It worked! If we have a record as need (optional) to restart (10 times) for next 3 days in column 2; how to get the value 10 from it? The query now returns 3 for this situation.

Tanvir Over a year ago

Plus if there is comma in that value (for example: need (optional) to restart (1,000 times) for next 3 days -how to avoid comma and get 1000 from it?

leftjoin Over a year ago

@Tanvir select regexp_extract('need (optional) to restart (1,000 times) for next 3 days','([0-9,]+)',1)

deathstroke · Accepted Answer · 2020-06-17 05:59:31Z

0

If you use the below regex it will work for both the columns and extract only the number from the string.

var numberPattern = /\d+/g;
'The first value is 200 gb'.match( numberPattern ).join('') // 200

'need to restart (2 times)'.match( numberPattern ).join('') // 2

answered Jun 17, 2020 at 5:59

deathstroke

5937 silver badges19 bronze badges

Comments

karan arora · Accepted Answer · 2020-06-17 07:01:45Z

0

Please try this
select REGEXP_REPLACE('The first value is 200 gb','[^0-9]','')
I got result as 200
Try this :
select REGEXP_REPLACE(substring('The 2nd value is 200 gb',-6),'[^0-9]','')

edited Jun 17, 2020 at 7:01

answered Jun 17, 2020 at 6:49

karan arora

18410 bronze badges

Collectives™ on Stack Overflow

Hive extract numeric value from a string

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related