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
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 |