Hiveql - how to keep only digits and spaces in string

Question

To be more specific, if the original string column is like

what I need eventually is the first cluster of numbers only, like

TIA

score 0 · Accepted Answer · answered Jun 23 '18 at 08:30

0

Use regexp_extract function:

select regexp_extract('AAA 123 BBB 456','[0-9]+',0) - digit one or more times

returns 123

answered Jun 23 '18 at 08:30

leftjoin

Thanks. It works perfectly. My understanding is that the final zero input is a group index, not a match index, so if there are multiple matches in a string, regexp_extract will always return the first one. Am I right? – pingboing Jun 25 '18 at 19:28
@pingboing Right, this is a regex group index, not a match index – leftjoin Jun 25 '18 at 19:30
Is there an easy way to return other matches (say, the 2nd match) whenever there are multiple? – pingboing Jun 25 '18 at 19:44
You need to write more complex regex. Like in this answer: https://stackoverflow.com/a/23556649/2700344 – leftjoin Jun 25 '18 at 19:51
appreciate your help – pingboing Jun 25 '18 at 19:57

1 Answers1