Sql script to find invalid email addresses

 

A data import was done from an access database and there was no validation on the email address field. Does anyone have an sql script that can return a list of invalid email addresses (missing @, etc).

Thanks! 

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

Anything more complex will likely return false negatives and run slower.

Validating e-mail addresses in code is virtually impossible.

EDIT: Related questions

  • I've answered a similar question some time ago: TSQL Email Validation (without regex)
  • T-SQL: checking for email format
  • Regexp recognition of email address hard?
  • many other Stack Overflow questions

    • Here is a quick and easy solution: 

    CREATE FUNCTION dbo.vaValidEmail(@EMAIL varchar(100))

RETURNS bit as
BEGIN     
  DECLARE @bitRetVal as Bit
  IF (@EMAIL <> '' AND @EMAIL NOT LIKE '_%@__%.__%')
     SET @bitRetVal = 0  -- Invalid
  ELSE 
    SET @bitRetVal = 1   -- Valid
  RETURN @bitRetVal
END

    Then you can find all rows by using the function: 

    SELECT * FROM users WHERE dbo.vaValidEmail(email) = 0

    If you are not happy with creating a function in your database, you can use the LIKE-clause directly in your query: 

    SELECT * FROM users WHERE email NOT LIKE '_%@__%.__%'

    Source

    I find this simple T-SQL query useful for returning valid e-mail addresses 

    SELECT email
FROM People
WHERE email LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0

    The PATINDEX bit eliminates all e-mail addresses containing characters that are not in the allowed az, 0-9, '@', '.', '_' & '-' set of characters.

    It can be reversed to do what you want like this: 

    SELECT email
FROM People
WHERE NOT (email LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0)
    链接地址: http://www.djcxy.com/p/92750.html

    上一篇: 正则表达式匹配模式，或者是空字符串

    下一篇: Sql脚本来查找无效的电子邮件地址