How to extract all the numbers from the hive data?

1 year ago

Sophia Anderson

1 minute

In Hive, the regular expression function regexp_extract_all can be used to extract all numbers from the data.

For example, suppose there is a table named “data” containing a column named “text” that stores some text data with numbers. You can use the following method to extract all the numbers:

SELECT regexp_extract_all(text, '[0-9]+') AS numbers
FROM data;

The above statement will extract all numbers from the text column and return them as an array. To return each number on a separate line, you can use the explode function.

SELECT explode(regexp_extract_all(text, '[0-9]+')) AS number
FROM data;

This will return a result set with each number as a separate row.