PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – Extract Value Between First Set of Parenthesis

Consider a string like Ana (16) and Brian (14) are my children.

How to get the value of the first parenthesis? ('16' in this case.)

I tried with substring('Ana (16) and Brian (14) are my children.' from '((.+))')

But it gives wrong result as 16) and Brian (14. (I can get it through strpos and substring combination. But need a better solution with regexp.)

Tags:

2

Answers


  1. 0

    Your regex is almost correct, you just need to use non-greedy repetition to match the shortest string: +? instead of just +:

    # select substring('Ana (16) and Brian (14) are my children.' from '((.+?))');
 substring
-----------
 16
(1 row)
    Login or Signup to reply.
  2. 0

    I like to use the regex functions, like regexp_matches. This one works:

    SELECT (regexp_matches('Ana (16) and Brian (14) are my children.', '(([^)]+))'))[1];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search