Remove Phone Numbers & Email Addresses in a string SQL

Question

I have a Table with Field "UserId" in Postgres

The List Contains a list of UserIds (comma separated) but also might contain email addresses and phone numbers .

Example

ID	UserID
1	11111,22222,+9199999999,xyz@yxz.com
3	2222,3333,11,+887777777,abc@bca.com

I want to remove all the phone-numbers and Email addresses and get the list of all userids comma separated in a new field.

OUPUT

ID	UserID
1	11111,22222
3	2222,3333,11

Also it will be better to have the query being optimised as it will be a part of a much complex query and should not not impact the performance .

Can someone suggest an ideal way to do it ?

Thanks

I have tried SUBSTRING , SPLITPART and Case Conditions based on it , but couldn't come out with a proper solution.

Answer 1

Use the REGEXP_REPLACE function to remove phone numbers.

UPDATE tablename SET columnname = REGEXP_REPLACE(columnname, '(\+[0-9]{1,3}[- ]?)?[0-9]{10,}', '');

Use the REGEXP_REPLACE function to remove email addresses.

UPDATE tablename SET columnname = REGEXP_REPLACE(columnname, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}', '');