REGEXP_SUBSTR use /g flag

elghalielghali Registered User

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?

Answers

  • KWilletsKWillets Registered User

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file