Postgres Regular Expression Positive Lookbehind with Repetition

Working for the first time with Postgres flavor regular expressions and stumped on it’s behavior here.

Input: 'xxxaaabc'

Expected: 'xxxaaazzzbc'

Logic: Match M a’s, capture group, and add ‘zzz’ directly after the sequence

Attempts:

select regexp_replace('aaabc','(?!\Sa+)','zzz','i')

Result: zzzxxxaaabc

select regexp_replace('aaabc','(?!\Sa+)','zzz','i')

Result: xxxazzzaabc

select select regexp_replace('xxxaaabc','(?!=a+)','zzz','i')

Result: zzzxxxaaabc

This one seems to get pretty close regexp_replace(‘aaabc’,'(?!\Sa+)’,’zzz’,’i’), but the repetition of a’s isn’t working.

>Solution :

Note the (?!\Sa+) pattern matches an empty location that is not immediately followed with a single non-whitespace char and then one or more a chars.

The (?!=a+) pattern matches a location in a string, that is not immediately followed with = and then one or more as.

You can capture one or more as and then use a backreference to the match and then just append the zzz:

select regexp_replace('xxxaaabc','(a+)','\1zzz','i') AS Result;

See the DB fiddle.