On Wed, Jan 22, 2025 at 12:53 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > jian he <jian.universal...@gmail.com> writes: > > https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP > > > """ > > or as a plain three-argument function: > > substring(string, pattern, escape-character) > > """ > > > but here "escape-character" is optional. > > > substring(string, pattern [,escape-character]) > > would be more accurate. > > No, the text is correct as written. substring(text, text) is a > completely different function that implements POSIX regular > expressions, not SQL regular expressions. It's described in > the next section (9.7.3). For example, > > regression=# select substring('foobar', 'o.b'); > substring > ----------- > oob > (1 row) > > regression=# select substring('foobar', 'o.b', ''); > substring > ----------- > > (1 row) > > because '.' is a metacharacter in POSIX but not SQL regexps. >
Thanks for the explanation. in section 9.7.2, substring(string, pattern, escape-character) the pattern must match the entire data string. (SQL standard) in section 9.7.3. substring(string, pattern) the pattern only needs part of the data string. (POSIX) I think the above is the main/big difference? in 9.7.2 do you think it's worthwhile changing it to "" As with SIMILAR TO, substring(string, pattern, escape-character) the specified pattern must match the entire data string, or else the function fails and returns null. "" ?
