PostgreSQL substring get string between brackets

Question

I have a string, say:

Product Description [White]

I want to extract anything inside the brackets (in this case White) from that string, using the PostgreSQL Substring function. I can get this to work using regexp_matches, but that returns an array which I don't want unless I have no other choice.

I've tried:

substring('string' from '[(.)]') >>> NULL
substring('string' from '\[(.)\]') >>> NULL
substring('string' from '\\[(.)\\]') >>> NULL

But this works:

substring('string' from 'W(.)i]') >>> h

What am I doing wrong?

user330315 · Accepted Answer · 2015-12-17 15:38:27Z

26

(.) only matches a single character, but you want to match multiple characters in there.

So you need (.+)

substring('Product Description [White]' from '\[(.+)\]')

answered Dec 17, 2015 at 15:38

user330315

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

5 Comments

gnarlybracket Over a year ago

Only missing one character... Thanks a lot!

lambda8 Over a year ago

your response helped me a lot. Thanks!!

user3871 Over a year ago

How can you do this for all matches in brackets

wheeleruniverse Over a year ago

This doesn't work with multiple brackets. "Product Description [White][Black] would return White][Black... looking for a way to improve this to fit my needs.

Christian Over a year ago

WHAT IF? substring('Product Description [White] [Blue]' from '[(.+)]')

Pradip Wawge · Accepted Answer · 2017-11-26 09:34:37Z

-2

i think you want to update some value like grade pay from pay scale or any thing just use following query

UPDATE master.pay_scale SET grade_pay= Case When description LIKE '%(%)%' Then (select substring(description from '((.+))')) else description End::numeric

edited Nov 26, 2017 at 9:34

answered Nov 26, 2017 at 9:29

Pradip Wawge

1314 bronze badges

Collectives™ on Stack Overflow

PostgreSQL substring get string between brackets

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related