Deleting certain pieces of values in between delimiters- SQL server

Question

What I am trying to do is delete certain data from an email column.

Every email with @w.com, @x.com, @y.com, and @z.com must be deleted from the emails

The problem I have is that there are more than one emails in each row for the email column, these email addresses are separated like

john@daddsa.com; johnsemail@aadsss.com; johnmainemail@z.com; johnthrowemail@y.com 

(using the semicolon ; as a delimiter)

There are many rows that contain the email addresses I am trying to delete. The email column is vch.

All email addresses vary in length.

I'm guessing this is done by running some sort of loop.

Thank you in advance.

Answer 1

1. Convert the string into rows using the delimiter ;
2. From the converted rows remove the unwanted Emails.
3. After removing convert the rows into single string delimited with ;

CREATE TABLE #delemail (email VARCHAR(5000))

INSERT INTO #delemail   VALUES     ( 'john@daddsa.com; johnsemail@aadsss.com; johnmainemail@z.com; johnthrowemail@y.com')

DECLARE @email VARCHAR(500)='' 


SELECT @email += ';' + emailrow
FROM   (SELECT Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))) emailrow
        FROM   (SELECT Cast ('<M>' + Replace(email, ';', '</M><M>') + '</M>' AS XML) AS Data
                FROM   #delemail) AS A
               CROSS APPLY Data.nodes ('/M') AS Split(a)) OU
WHERE  emailrow NOT LIKE '%@w.com%'
       AND emailrow NOT LIKE '@x.com%'
       AND emailrow NOT LIKE '%@y.com%'
       AND emailrow NOT LIKE '%@z.com%'

SELECT RIGHT(@email, Len(@email) - 1) As OrgEmail

OrgEmail

john@daddsa.com;johnsemail@aadsss.com

The above query can be convert to a Scalar Function.

CREATE FUNCTION Removefakmail (@email VARCHAR(5000))
returns VARCHAR(5000)
AS
  BEGIN
      DECLARE @newemails VARCHAR(5000)=''

      SELECT @newemails += emailrow + '; '
      FROM   (SELECT Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))) emailrow
              FROM   (SELECT Cast ('<M>' + Replace(@email, ';', '</M><M>') + '</M>' AS XML) AS Data) AS A
                     CROSS APPLY Data.nodes ('/M') AS Split(a)) OU
      WHERE  emailrow NOT LIKE '%@w.com%'
             AND emailrow NOT LIKE '@x.com%'
             AND emailrow NOT LIKE '%@y.com%'
             AND emailrow NOT LIKE '%@z.com%'

      SELECT @newemails = LEFT(@newemails, Len(@newemails) - 1)
      RETURN @newemails
  END

SELECT dbo.Removefakmail(email)
FROM   #delemail 

OrgEmail

john@daddsa.com;johnsemail@aadsss.com

Answer 2

If you have a table like this:
Email:
Row1= Email1;Email2;Email3
Row2= Email1;Email2;Email3;Email4 ...

For deleting/updating some emails from the email column you can use CLR functions or use PATINDEX statement(inside WHILE statement).

Maybe this links help you:

Regex pattern inside SQL Replace function?.
CLR Integration.
How can I make a REPLACE PATTERN in SQL?.