REGEXP_SUBSTR use /g flag

elghali · March 2018

Hello, I am trying to extract all occurrences of a word before '=' in a string, i tried to use this regex '/\w+(?=\=)/g' but it returns null, when i remove the first '/' and the last '/g' it returns only one occurrence that's why i need the global flag, any suggestions?

KWillets · March 2018

REGEX_SUBSTR is a single-valued function, but you can call it with different values of the occurrence parameter by using a cross join.

SELECT REGEXP_SUBSTR( MYCOL, 'w+(?=\=)', 1, OCC )
FROM MYTABLE 
CROSS JOIN
(SELECT row_number() AS OCC OVER () x FROM tables) foo
WHERE REGEXP_SUBSTR( MYCOL, 'w+(?=\=)', 1, OCC ) IS NOT NULL

REGEXP_SUBSTR use /g flag

Answers

Leave a Comment