Search code examples
prestoregex-lookarounds

In Presto SQL, how to extract all substrings followed by a pattern? Each word in the string is separated by space

Input string: "abc_11234 abc_11235 xyz78763 abc_32342" Expected output [11234, 11235, 32342]

The goal is trying to get all the substrings after abc_ in each string separated by space. I've tried using REGEXP_EXTRACT_ALL(v, 'abc_\s*(.+)') but the pattern extracts everything after the first abc_

Solution

  • You can use

    SELECT regexp_extract_all(v, 'abc_\s*(\S+)', 1);

    See the regex demo. \S+ matches one or more non-whitespace chars. 1 means the contents of Group 1 are only returned.

    See more about how REGEXP_EXTRACT_ALL function works.