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

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

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.

CodePudding user response：

In MSSQL, I have had to do a similar thing when converting data returned from an API. One of the columns had a comma delimited list of users.

SELECT 
     [id]
    ,value AS 'UserID'
FROM [dbo].[table]
CROSS APPLY STRING_SPLIT(UserID, ',')

But I don't know how to handle this with Postgres. I tried to use ChatGPT, but the server is at capacity.

The below Stack Overflow link shows how to convert MSSQL CROSS APPLY functionality to Postgres by using a parameterized function. I'd figure it out for you, but I don't have a Postgres environment to make sure it works properly.

Postgres analogue to CROSS APPLY in SQL Server

Hope this helps.

CodePudding user response：

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,}', '');