Skip to main content
Question

REGEXP_SUBSTR use /g flag

  • March 6, 2018
  • 1 reply
  • 3 views
elghali
Forum|alt.badge.img+1

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?

    1 reply

    KWillets
    • KWillets
    • New Participant
    • March 22, 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
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement