REGEXP_SUBSTR to extract a substring from Vertical SQL

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 -APName- and -slotId-

UPDATE

Since Vertica’s regular expression functions use PCRE, tagging this with other relevant tags.

Answer

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