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;

Use `regexp_extract()` to extract the numbers, or use `substr()` to extract that if the position is same always. — samkart, Dec 29 '19 at 20:23

score 1 · Accepted Answer · answered Dec 29 '19 at 20:53

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

See other examples in:

1 Answers1