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).
14 Answers
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 '_%@__%.__%'
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
Postgres version:
select user_guid, user_guid email_address, creation_date, email_verified, active
from user_data where
length(substring (email_address from '%[ &'',":;!+=\/()<>]%')) > 0 -- Invalid characters
or length(substring (email_address from '[@.-_]%')) > 0 -- Valid but cannot be starting character
or length(substring (email_address from '%[@.-_]')) > 0 -- Valid but cannot be ending character
or email_address not like '%@%.%' -- Must contain at least one @ and one .
or email_address like '%..%' -- Cannot have two periods in a row
or email_address like '%@%@%' -- Cannot have two @ anywhere
or email_address like '%.@%' or email_address like '%@.%' -- Cant have @ and . next to each other
or email_address like '%.cm' or email_address like '%.co' -- Unlikely. Probably typos
or email_address like '%.or' or email_address like '%.ne' -- Missing last letter
;
On sql server 2016 and up
CREATE FUNCTION [DBO].[F_IsEmail] (
@EmailAddr varchar(360) -- Email address to check
) RETURNS BIT -- 1 if @EmailAddr is a valid email address
AS BEGIN
DECLARE @AlphabetPlus VARCHAR(255)
, @Max INT -- Length of the address
, @Pos INT -- Position in @EmailAddr
, @OK BIT -- Is @EmailAddr OK
-- Check basic conditions
IF @EmailAddr IS NULL
OR @EmailAddr NOT LIKE '[0-9a-zA-Z]%@__%.__%'
OR @EmailAddr LIKE '%@%@%'
OR @EmailAddr LIKE '%..%'
OR @EmailAddr LIKE '%.@'
OR @EmailAddr LIKE '%@.'
OR @EmailAddr LIKE '%@%.-%'
OR @EmailAddr LIKE '%@%-.%'
OR @EmailAddr LIKE '%@-%'
OR CHARINDEX(' ',LTRIM(RTRIM(@EmailAddr))) > 0
RETURN(0)
declare @AfterLastDot varchar(360);
declare @AfterArobase varchar(360);
declare @BeforeArobase varchar(360);
declare @HasDomainTooLong bit=0;
--Control des longueurs et autres incoherence
set @AfterLastDot=REVERSE(SUBSTRING(REVERSE(@EmailAddr),0,CHARINDEX('.',REVERSE(@EmailAddr))));
if len(@AfterLastDot) not between 2 and 17
RETURN(0);
set @AfterArobase=REVERSE(SUBSTRING(REVERSE(@EmailAddr),0,CHARINDEX('@',REVERSE(@EmailAddr))));
if len(@AfterArobase) not between 2 and 255
RETURN(0);
select top 1 @BeforeArobase=value from string_split(@EmailAddr, '@');
if len(@AfterArobase) not between 2 and 255
RETURN(0);
--Controle sous-domain pas plus grand que 63
select top 1 @HasDomainTooLong=1 from string_split(@AfterArobase, '.') where LEN(value)>63
if @HasDomainTooLong=1
return(0);
--Control de la partie locale en detail
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890!#$%&‘*+-/=?^_`.{|}~'
, @Max = LEN(@BeforeArobase)
, @Pos = 0
, @OK = 1
WHILE @Pos < @Max AND @OK = 1 BEGIN
SET @Pos = @Pos + 1
IF @AlphabetPlus NOT LIKE '%' + SUBSTRING(@BeforeArobase, @Pos, 1) + '%'
SET @OK = 0
END
if @OK=0
RETURN(0);
--Control de la partie domaine en detail
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890-.'
, @Max = LEN(@AfterArobase)
, @Pos = 0
, @OK = 1
WHILE @Pos < @Max AND @OK = 1 BEGIN
SET @Pos = @Pos + 1
IF @AlphabetPlus NOT LIKE '%' + SUBSTRING(@AfterArobase, @Pos, 1) + '%'
SET @OK = 0
END
if @OK=0
RETURN(0);
return(1);
END
SELECT Email FROM Employee WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
SELECT EmailAddress AS ValidEmail
FROM Contacts
WHERE EmailAddress LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0
GO
Please check this link: https://blog.sqlauthority.com/2017/11/12/validate-email-address-sql-server-interview-question-week-147/
sel 'unismankur@yahoo#.co.in' as Email,
case
when Email not like '%@xx%'
AND Email like '%@%'
AND CHAR_LENGTH(
oTranslate(
trim( Email),
'._-@0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'')
) = 0
then 'N' else 'Y' end as Invalid_Email_Ind;
This works very well for me.
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)
Related