Regex_replace in the Field Calculator: unexpected duplication of replacement elements

Question

After testing things out for a different question, I found something unexpected about the way regex_replace works in the Field Calculator, compared to PostgreSQL. The suggested insert ends up duplicated in the output.

The string: 'a_a_a_a_a'.
The desired output: 'a_a_a_a_ca'

I'm testing with two patterns. The first finds all the non-underscore characters at the end of a string, while the second finds the first non-underscore character after the fourth underscore. The patterns:

([^_]*)$
(?<=[^_]_[^_]_[^_]_[^_]_)([^_]{1})

PostgreSQL regex_replace behaviour works as I would expect:

select string,
regexp_replace(string,'([^_]*)$','c\1') as repl_1,
regexp_replace(string,'(?<=[^_]_[^_]_[^_]_[^_]_)([^_]){1}','c\1') as repl_2
from (select 'a_a_a_a_a' as string) pattern

Outcome:

string	repl_1	repl_2
a_a_a_a_a	a_a_a_a_ca	a_a_a_a_ca

Field Calculator behaves differently, using the same pattern:

regexp_replace('a_a_a_a_a','([^_]*)$','c'), as shown by the Output preview, gives 'a_a_a_a_cc'. Interestingly, the capture group doesn't even have to be called with \1 to get that output.

When I do call the capture group, I again end up getting an unexpected result:

regexp_replace('a_a_a_a_a','([^_]*)$','c\\1') returns 'a_a_a_a_cac'

Using the positive lookbehind in the Field Calculator works fine, however:

regexp_replace('a_a_a_a_a','(?<=[^_]_[^_]_[^_]_[^_]_)([^_]{1})','c\\1')

Returning 'a_a_a_a_ca' as expected.

Is this just a weird bug with regex_replace in the Field Calculator, or am I missing something obvious?

Answer 1

In your regex matching pattern ([^_]*)$, replace * (zero or more repetitions) by + (one or more repetitions), then it works:

regexp_replace('a_a_a_a_a','([^_]+)$','c\\1') returns 'a_a_a_a_ca'

---

Explanation

As written in help, the replacement string will "replace any matching occurrences of the supplied regular expression" (thus: not just one). Using the quantifier * (zero or more repetitions) finds in fact two matching patterns:

1. the last charcter a (one repetition of a non undscore character)
2. the end of the string after the last character: the "empty" end of the string: zero repetition of a non undscore character.

This becomes clear when the last character in the string is an underscore:

regexp_replace('a_a_a_a_','([^_]*)$','c\\1') returns 'a_a_a_a_c'

So the expression gets the end of the string with a zero-repetition of a non-underscore character and replaces this by c. This might seem unexpected, but is logic. For this reason, stick to quantifier + to get at least one non-underscore character.