0 Replies Latest reply on Dec 10, 2015 8:50 AM by JMessi

    Find bad email address using SQL Queries

    JMessi Apprentice

      SELECT contact1.company,contact1.contact,cs.accountno,contsupref+cs.address1,cs.address2,cs.zip

      FROM contsupp cs,contact1

      WHERE   (cs.CONTACT = 'E-mail Address') and contact1.accountno=cs.accountno

        AND (SELECT

         CASE

         WHEN cs.contsupref IS NULL

         OR CHARINDEX('@.',cs.contsupref+cs.address1) > 0

         OR CHARINDEX('[email protected]',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