I am trying to run the below query to get a substring from a string but it gives wrong result. I am not sure how to use the regex since the regex I am using works well with PCRE.
SELECT REGEXP_SUBSTR( 'Wireless-Interface-APName-ndehqb-a3g4-a10-slotId-1' , '.*-APName-(.*)-slotId.*' )
I am expecting to get
ndehqb-a3g4-a10 in this case but it prints the original string itself. The ask is to get the substring that matches anything between
Since Vertica’s regular expression functions use PCRE, tagging this with other relevant tags.
You are not trying to get the whole pattern (that would be the whole input string), but the first subexpression that you capture in round parentheses.
This makes the call a bit more complex – and I always have to look it up in the docu, as I never seem to be able to memorise all possible parameters and their order.
Here is how I would call it, with commented parameters:
SELECT REGEXP_SUBSTR( 'Wireless-Interface-APName-ndehqb-a3g4-a10-slotId-1' -- in string , '.*-APName-(.*)-slotId.*' -- pattern , 1 -- search start position , 1 -- occurrence , '' -- modifier , 1 -- captured subexp number ) AS captured_subexp ; -- out captured_subexp -- out ----------------- -- out ndehqb-a3g4-a10