postgresqlstring-function

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.)

Solution

  • 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)