Getting unexpected results from regexp_replace

From: Dan Nessett <dnessett(at)yahoo(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Getting unexpected results from regexp_replace
Date: 2021-02-21 15:37:45
Message-ID: BED25D59-6AB4-472E-901A-9223CA2C6201@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I freely admit this may be my problem. Writing regular expression patterns is more an art than a skill. However, I am getting an unexpected result from regex_replace().

I have a table that is partially defined as follows (names and email addresses hidden for privacy):

user_name user_email
“AAAAA"
“BBBBB” “bbbbb(xxxxx)"
“CCCCC” "ccccct(home)"
“DDDDD"
“EEEEE" "aeeeee(home)”

The second entry is an email address - bbbbb - followed by the name of an individual (xxxxx) in parentheses. The email address for CCCCC and EEEEE have the word “home” in parentheses appended to the email address.

I want to delete the parenthetical expression including the parentheses for all email addresses. I also have a column (not shown) called email_list that contains a comma separated list of all email addresses associated with each name or NULL if there is no list. I create a table:

CREATE TABLE "households_with_email" AS
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 != ‘';

I expected the regex_replace to the parenthetical text with the null character. Instead, it replaces the whole string in user_email with the null string:

user_name user_email
"Rodriguez” ""
"Armstrong" ""
"Bauer" ""
"Berst" ""
"Berst” ""

I realize there may be some characteristic such as greedy matching that is causing this result, but if so, I don’t see how. The pattern indicates first find the ‘(‘ character, then match all characters until a ‘)’ character arrives. Those characters, including the parentheses should then be replaced with the null string.

Or am I misinterpreting the pattern?

Dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-02-21 15:50:30 Re: Getting unexpected results from regexp_replace
Previous Message Thomas Kellerer 2021-02-20 20:01:23 Re: JSONB_AGG: aggregate function calls cannot be nested