ansaurus

Question

Sql script to find invalid email addresses

Answer 1

+3 A:

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

Espo 2009-04-29 06:51:31

+1 for the UDF.

Tomalak 2009-04-29 07:11:33

Answer 2

A:

here you go

http://sqlteam.com/forums/topic.asp?TOPIC_ID=19434 http://www.citafm.com/codepoet/index.cfm?id=429

Vikram 2009-04-29 06:51:39

Answer 3

+6 A:

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

Tomalak 2009-04-29 06:53:15

I've used this one and it has not failed me in years. I consider myself pretty good at regexs but I think a cylon wrote this http://ex-parrot.com/~pdw/Mail-RFC822-Address.html

Chad Grant 2009-04-29 07:12:48

Thanks, this is awesome! I did a search of stackoverflow but I should have used google search :/Cheers!

campo 2009-04-29 12:23:43

Already too complex and wrong. foo@bar is a legal email address (providing the ".bar" TLD exists and has either an address or a MX record).

bortzmeyer 2009-06-16 09:43:03

Calling this even "unlikely" would be very British already. The expression is not for validating e-mail addresses or checking every corner case. It is a basic sanity check that covers 99.9% of all cases without yielding false negatives, and I did not indicate otherwise.

Tomalak 2009-06-16 11:18:44

Validating e-mail addresses with a regular expression (not for the faint of heart): http://www.regular-expressions.info/email.html (as you see, validating standard e-mail addresses is a LOT harder than validating common email addresses)

iconiK 2010-03-22 06:34:25

foo@bar is going to crop up on the public Internet in the not too distant future too: http://www.canon.com/news/2010/mar16e.html

David Dorward 2010-03-22 06:35:22

Answer 4

A:

select * from users WHERE NOT ( CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 AND LEFT(LTRIM([Email]),1) <> '@' AND RIGHT(RTRIM([Email]),1) <> '.' AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1 AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3 AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0)

2009-06-16 05:56:18

Answer 5

A:

select email 
from loginuser where
patindex ('%[ &'',":;!+=\/()<>]%', email) > 0  -- Invalid characters
or patindex ('[@.-_]%', email) > 0   -- Valid but cannot be starting character
or patindex ('%[@.-_]', email) > 0   -- Valid but cannot be ending character
or email not like '%@%.%'   -- Must contain at least one @ and one .
or email like '%..%'        -- Cannot have two periods in a row
or email like '%@%@%'       -- Cannot have two @ anywhere
or email like '%.@%' or email like '%@.%' -- Cant have @ and . next to each other
or email like '%.cm' or email like '%.co' -- Unlikely. Probably typos 
or email like '%.or' or email like '%.ne' -- Missing last letter

This worked for me. Had to apply rtrim and ltrim to avoid false positives.

Source: http://sevenwires.blogspot.com/2008/09/sql-how-to-find-invalid-email-in-sql.html

Manishm 2010-03-22 06:21:44

ansaurus

tags:

views:

answers:

Sql script to find invalid email addresses

related questions