how to extract a part of a string in hive

Question

I have a column called desc and it contains the below string :

BUY 20 SAVE 5

Desired output: 20

I tried:

SELECT
   desc,
   split (desc, 'Y\\s')[1] as Revenue
FROM table1;

Answer 1

Using Hive regexp_extract(string subject, string pattern, int index) function:

SELECT regexp_extract(desc, '.*? (\\d+) .*$', 1) AS Revenue
  FROM table1

See other examples in:

• "Hive QL selecting numeric substring of string"
• "extracting a substring from a text column in hive"