Extract number between two characters in Hive SQL

user_12711917 0
Viewed 235

The query below outputs 1642575.0. But I only want 1642575 (just the number without the decimal and the zero following it). The number of delimited values in the field varies. The only constant is that there's always only one number with a decimal. I was trying to write a regexp function to extract the number between " and ..

How would I revise my regexp_extract function to get the desired output? Thank you!

select regexp_extract('{"1244644": "1642575.0", "1338410": "1650435"}','([1-9][0-9]*[.][0-9]+)&*');

hiveql numeric hive sql regex
2 Answers

You can cast the result to bigint.

select cast(regexp_extract('{"1244644": "1642575.9", "1338410": "1650435"}','([1-9][0-9]*[.][0-9]+)&*') as bigint) col;
output - 1642575

You can use round if you want to round it off.

select round(regexp_extract('{"1244644": "1642575.9", "1338410": "1650435"}','([1-9][0-9]*[.][0-9]+)&*')) col;
output - 1642576
user_64305230

Use this regexp: '"(\\d+)\\.' - means double-quote, capturing group with one or more digits, dot.

select regexp_extract('{"1244644": "1642575.9", "1338410": "1650435"}','"(\\d+)\\.',1)

Result:

1642575

To skip any number of leading zeroes, use this regexp: '"0*(\\d+)\\.'

user_27003440
Related