Remove Specific Email Address From Multiple Rows

One of our customers had this problem where they add the Customer Service Manager’s address to all accounts they manage manually. When this guy leaves the company, someone has to use the UI and manually go through thousands of records one by one and remove the email id. The column which stores these email values in the table sometimes has multiple emails separated by a comma(,) and the specific email that needs to be removed could be at the beginning, end or any any other random place in the string. We wanted to create a stored procedure which accepts target email as parameter and deletes all occurrences. This whole thing appeared too complex until I figured out all it takes is an update command and use of TSQL function REPLACE .

Lets say we would like to remove [email protected] . Who in his right mind would want to keep the Joker :). Lets have some test data to play around.

/* Create a test table */
CREATE TABLE EmailTest 
(
	id int identity,
    name nvarchar(100),
    email nvarchar(500)
   ) 
/* Insert some random data */ 
/*The following is not a valid record for our test */
  INSERT INTO EmailTest VALUES ('Nikola Tesla', '[email protected]')
/* 'Joker' is what we are going to update/remove. Lets insert a string with a lone email id. */
  INSERT INTO EmailTest VALUES ('Joker', '[email protected]')
/* What if the email id is at the beginning of the string? */
  INSERT INTO EmailTest VALUES ('JokerAndCo', '[email protected],[email protected],[email protected],[email protected],[email protected]')
/* Email at the end of the string */
  INSERT INTO EmailTest ('Alan Turing', '[email protected],[email protected],[email protected],[email protected],[email protected]') 
/* Email at a random position */
  INSERT INTO EmailTest VALUES ('Elon Musk', '[email protected],[email protected],[email protected],[email protected],[email protected]')

The actual stored procedure. Its self explanatory.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RemoveEmail]
	@RemEmail NVARCHAR(100)
AS

DECLARE @Length int
DECLARE @Delimiter char
SELECT @Delimiter = ','
SELECT @Length = LEN(@RemEmail)

UPDATE EmailTest SET email = REPLACE(email, @RemEmail, '') WHERE LEN(email) = @Length #lone record
UPDATE EmailTest SET email = REPLACE(email, @RemEmail[email protected]Delimiter, '') WHERE CHARINDEX(@RemEmail, email) = 1 #beginning of string
UPDATE EmailTest SET email = REPLACE(email, @Delimiter[email protected]RemEmail, '') WHERE CHARINDEX(@Delimiter, REVERSE(email)) - @Length  = 1 #end of the string
UPDATE emailtest SET email = REPLACE(email, @RemEmail[email protected]Delimiter, '') #random position in the string

Execute stored procedure

EXECUTE dbo.RemoveEmail '[email protected]'

Hope this is helpful to someone. Enjoy!

comments powered by Disqus