CODEWITHSUNDEEP
postgresqlstring-function
Iniyavan
Iniyavan

Reputation: 115

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

Upvotes: 0

Views: 31

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 126970

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];

Upvotes: 1

Dogbert
Dogbert

Reputation: 222040

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)

Upvotes: 2

Related Questions