Jay R Blog

Ugly Marketing List Cleaned Up

A client of mine purchased an opt-in b2b marketing list, and wanted to send an email to everyone on the list. Yes, I know, quasi-spam. He's not sending it from any of my servers, so he'll have to find somewhere else to do so. At any rate, he needed the data cleaned up for him, so he sends it over to me. The only two columns really needed are the name and email column, as he wants each email to address the person by name. The problem with this list is that there are no exact duplicates, but there are many many occurences of entries that have the same email address, but a different name in the name column. No one email address should be emailed twice. Here's the SQL script I wrote to clean the data up for him:

DECLARE @T table(TName varchar(29), TEmail varchar(48))

INSERT @T(TName, TEmail)
SELECT M.Name, M.Email
FROM Marketing_REAgents M
WHERE (M.Email IN (SELECT Email FROM Marketing_REAgents GROUP BY Email HAVING (COUNT(Email) > 1)))
AND (M.ID = (SELECT TOP 1 ID FROM Marketing_REAgents WHERE (Email = M.Email)))
ORDER BY M.Email

DELETE
FROM Marketing_REAgents
WHERE (Email IN (SELECT Email FROM Marketing_REAgents
GROUP BY Email
HAVING (COUNT(Email) > 1)))

INSERT Marketing_REAgents(Email, Name) SELECT TEmail, TName FROM @T


I know similar duplicate removal scripts have been posted everywhere, but hey, this is my little corner of the web, so I'll act as if I just discovered the cure for cancer. While I'm here, I might as well post a useful function that has become part of my ASP.NET toolbox. It's basically the most efficient way (in terms of execution speed) I've found to see if a number is an integer. I wrote a forum in .NET that gets a lot of traffic and is on a community server, so every little piece of code that could be made quick, I tried to make quick. At any rate, I use this function a lot to make certain a valid, numeric integer is passed for a forum ID, topic ID, or whatever the case may be.

Function IsInt(ByVal strNumber As String) As Boolean
 If strNumber=Nothing
  Return(False)
 Else
  Dim X As Integer

  For X=0 To strNumber.Length-1
   If Not (Char.IsNumber(strNumber, X)) Then Return(False)
  Next

  Return(True)
 End If
End Function

In tests I ran, this function performed faster than using the old-school IsNumeric() function (that is in the VB class for .NET) and also performed faster than trying to parse to an integer using a Try Catch statement.

Ok, that's all for now. Just wanted to put something up for my first blog :)

Legacy Comments


mohdowais
2003-11-15
re: Ugly Marketing List Cleaned Up
Jay, I dont see how your IsInt() function handles decimals, negative numbers, and formatted numbers. I think all of the below fail the test:

200.39
-190
4,659.00

Good attempt at solving the problem, but unfortunately there are so many issues that we dont think of, that the MS Team did think of :-)

Jay
2004-01-30
re: Ugly Marketing List Cleaned Up
Well, basically the numbers that I am passing are only always going to be whole, positive integers. I just wanted to devise the quickest possible method to do this. Apologies for leaving this part out.