The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

REGEXP_SUBSTR use /g flag

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

  • 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.