Docs for `regexp_replace` do not mention that it applies to first occurrence only

[david-cortes]

Docs for function regexp_replace mention:

If string contains the regexp pattern, replaces the matching part with replacement (see Pattern Matching).

But what this function does by default is only to replace the first occurrence of the pattern, which is different from what other regex engines do which is to replace all of them.

See for example R's gsub in perl mode:

gsub("a", "", "baab", perl=TRUE)

"bb"

DuckDB in contrast would output "bab", which is not what one would expect from what the docs describe.

[szarnyasg]

Hello, thanks for reporting this.

The reason behind this is by default DuckDB/RE2 assume the g (global replace) option to be false.

The option is given here: https://duckdb.org/docs/sql/functions/regular_expressions#options-for-regular-expression-functions, but the documentation should be clearer about both the default value of this and also the regexp_replace function should be better documented.