PostgreSQL substring get string between brackets

问题:

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?

回答1:

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

So you need (.+)

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

回答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