Re: Getting unexpected results from regexp_replace

Thanks. Doubling the backslashes did the trick. I tried to use the original expression without the E, but postgres threw an error and said to use the “E” version of the pattern.

Dan

> On Feb 21, 2021, at 8:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dan Nessett <dnessett(at)yahoo(dot)com> writes:
>> SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, family_list, street_address, city, state, zip, phone_list, email_list
>> FROM "household_data"
>> WHERE email_list != ‘';
>
> Because you used E'...', the backslashes are eaten by the string literal
> parser. So the pattern seen by regexp_replace() is just '(.*)', in
> which the parens are capturing parens not literal characters. Thus it
> matches the whole string.
>
> Personally I'd leave off the E, but if you must use it then double the
> backslashes.
>
> regards, tom lane
>
>