postgres substring escape open bracket character

Question

I am trying to return all characters within brackets from a string field in postgres:

This function seems to work well:

 select substring('foo bar [foobar] foo' from '%#"f%b#"%' for '#');
 substring
-----------
 foob

It will not work with an open bracket character:

select substring('foo bar [foobar] foo' from '%#"[%]#"%' for '#');
substring
-----------

The close bracket works fine, I've tried backslash, double backslash, period, single quote

Answer 1

You should escape brackets as they are metacharacters:

select substring('foo bar [foobar] foo' from '%#"#[%#]#"%' for '#');

 substring 
-----------
 [foobar]
(1 row) 

or

select substring('foo bar [foobar] foo' from '%#[#"%#"#]%' for '#');

 substring 
-----------
 foobar
(1 row)

From the documentation:

In addition to these facilities borrowed from LIKE, SIMILAR TO supports these pattern-matching metacharacters borrowed from POSIX regular expressions:

...

• A bracket expression [...] specifies a character class, just as in POSIX regular expressions.