9

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:

2

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:

3

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:

4

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?

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.