GoldMine SQL Query To Display Undeliverable / Malformed Emails

The following SQL Query will display all the email addresses in your GoldMine database that will not be deliverable if you attempt to send to the contacts associated with the email address.  Running this query will allow you to update/correct the addresses and cleanse your database for better email marketing.

Because the below SQL query is looking for special characters it doesn’t display properly in a web page – please cut and paste the text of the query from the following  link: Find Malformed Email Addresses

SELECT
 accountno
,cs.contsupref + cs.Address1 AS ‘E-Mail Address’
FROM
 contsupp cs
WHERE
 (cs.CONTACT = ‘E-mail Address’)
 AND (SELECT
  CASE
  WHEN cs.contsupref + cs.Address1  IS NULL
  OR CHARINDEX(‘@.’,cs.contsupref + cs.Address1 ) > 0 
  OR CHARINDEX(‘.@’,cs.contsupref + cs.Address1 ) > 0 
  OR CHARINDEX(‘..’,cs.contsupref + cs.Address1 ) > 0 
  OR CHARINDEX(‘”‘, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘(‘, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘)’, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘,’, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘<‘, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘>’, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘;’, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘:’, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘[‘, cs.contsupref + cs.Address1 ) <> 0 
  OR CHARINDEX(‘]’, cs.contsupref + cs.Address1 ) <> 0 
  OR RIGHT(RTRIM(cs.contsupref + cs.Address1 ),1) = ‘.’ 
  OR CHARINDEX(‘ ‘,LTRIM(RTRIM(cs.contsupref + cs.Address1 ))) > 0 
  OR LEN(cs.contsupref + cs.Address1 )-1 <= CHARINDEX(‘.’, cs.contsupref + cs.Address1 ) 
  OR cs.contsupref + cs.Address1  LIKE ‘%@%@%’ 
  OR cs.contsupref + cs.Address1  NOT LIKE ‘%@%.%’ THEN 0
 ELSE 1
END) = 0