From NewHaven Software Wiki

Introduction

Chances are you already know that your customer list is one of your most valuable assets. To make the most of that list, be it for marketing or customer service, you'll want to ensure the email address you have for your customers is a valid one. While there is little you can do to validate an email address is deliverable (like our Address Validation Module can do for physical addresses) or matches the recipient (like NCOA processing) you can at least search for common mistakes in the entry of the email address so they can be corrected.

Identifying bad email addresses

The following SQL can be used in a custom SQL filter to identify customers who have an email address but there is something wrong with its formatting. This could be the lack of an @ sign, a missing .com at the end, a space somewhere in the middle or non-email type data.

SELECT C.*
FROM CUST C
WHERE EMAIL <> '' AND NOT
(CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND  LEFT(LTRIM([EMAIL]),1) <> '@'
AND  RIGHT(RTRIM([EMAIL]),1) <> '.'
AND  datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND  (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0))

Techniques for repairing

Now that you have your list of customers with bad email addresses, you can use one of the following methods to follow-up and correct them:

• QuickCount
• Export/Excel
  • Fix in CMS
  • Professional Services to import your changes
• Mail List Report

Future

There are services today that offer an 'email append service' to find the proper email for a customer. Presently CMS does not support an integration with any particular vendor that offers this service. It could, however, still be done via professional services. Please contact your Platinum Account Manager if you have an email append service company you'd like us to work with. We can coordinate with them to make sure they have the data they need and that we're able to update your customer records with the resulting email addresses.