PostgreSQL extract values between brackets

Question

I have a string in PostgreSQL 11:

'for values from ('2022-01-01') to ('2023-01-01')'

And I need to extract all values between brackets:

expected result: '2022-01-01', '2023-01-01'

If I use the following construction, I'm able to extract only the first value:

select substring('FOR VALUES FROM (''2020-01-01'') TO (''2021-01-01'')' from '\((.*?)\)');

Is it possible to extract the second value? Or both values as one array?

Gordon Linoff · Accepted Answer · 2021-06-16 11:21:10Z

1

You can use regexp_match():

select regexp_matches('for values from (''2022-01-01'') to (''2023-01-01'')', '\((.[^)]+)\)', 'g')

answered Jun 16, 2021 at 11:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

sticky bit · Accepted Answer · 2021-06-16 11:27:42Z

0

If there's only one level of parenthesis than you can use regexp_matches() with the g (global) flag.

SELECT *
       FROM regexp_matches('for values from (''2022-01-01'') to (''2023-01-01'')', '\([^\)]*\)', 'g');

Otherwise, if there are more levels, that'll get messy results though. In such a case you need to define which is the level you're after and regular expressions might be of limited use in such a case.

answered Jun 16, 2021 at 11:27

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

PostgreSQL extract values between brackets

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