PostgreSQL - Extract Value Between First Set of Parenthesis

Question

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

It's important when asking for help that instead of saying "it gives wrong results" that you actually include the wrong results. That's a key part of debugging, to see the wrong results. It gives important clues as to what is wrong. — Andy Lester
– Andy Lester, Commented Jul 2, 2024 at 17:48

Dogbert · Accepted Answer · 2024-07-02 17:43:52Z

2

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)

answered Jul 2, 2024 at 17:43

Dogbert

224k43 gold badges419 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2024-07-02 17:46:17Z

1

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

answered Jul 2, 2024 at 17:46

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Extract Value Between First Set of Parenthesis

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related