sql - 如何在 hive 中提取字符串的一部分

Question

我有一列称为desc，它包含以下字符串:

BUY 20 SAVE 5

所需的输出: 20

我试过了:

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

Answer 1

使用Hive regexp_extract(string subject, string pattern, int index) 函数:

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

请参阅以下示例:

“Hive QL selecting numeric substring of string”

“extracting a substring from a text column in hive”