postgresql - postgres子字符串转义开括号字符

Question

我试图从 postgres 中的字符串字段返回括号内的所有字符:

这个函数似乎运行良好:

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

它不适用于左括号字符:

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

右括号工作正常，我试过反斜杠、双反斜杠、句号、单引号

Answer 1

你应该转义方括号，因为它们是元字符:

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

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

或

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

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

来自 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.