Skip to content

Extract number between two characters in Hive SQL

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]+)&*');



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
10 People found this is helpful