How to get string between parenthesis?

WITH input(s) AS (SELECT '(success)' )
SELECT REPLACE(REPLACE(s,'(',''),')','') FROM input;

You can also use the TRANSLATE function:

dbadmin=> select translate('(success)', '()', '') "What's inside the Parentheses?";
 What's inside the Parentheses?
--------------------------------
 success
(1 row)

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/TRANSLATE.htm