Sql script to find invalid email addresses
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
T-SQL: checking for email format
I tested the following query with many different wrong and valid email addresses. It should do the job.
IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
print 'valid email address'
ELSE
print 'not valid'
It checks these conditions:
- No embedded spaces
- '@' can't be the first character of an email address
- '.' can't be the last character of an email address
- There must be a '.' somewhere after '@'
- the '@' sign is allowed
- Domain name should end with at least 2 character extension
- can't have patterns like '.@' and '..'
How to write a query to ensure email contains @
You can use LIKE with wildcards. See here for the wildcards on DB2.
The underscore character ( _ ) represents any single character.
The percent sign character (%) represents a string of zero or more characters.
SELECT email
FROM YourTable
WHERE email NOT LIKE '%_@__%.__%'
This will ignore the following cases (simple version for valid emails):
- emails that have at least one character before the @;
- emails that have at least two characters between @ and .;
- emails that have at least two characters between . and the end.
You can see an example in MySql in sqlfiddle.
To add it as a constraint, you do (as mustaccio said in a comment):
alter table your_table add constraint chk_email check (email like '%_@__%.__%')
TSQL Email Validation (without regex)
Very basic would be:
SELECT
EmailAddress,
CASE WHEN EmailAddress LIKE '%_@_%_.__%'
AND EmailAddress NOT LIKE '%[any obviously invalid characters]%'
THEN 'Could be'
ELSE 'Nope'
END Validates
FROM
Table
This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.
You can write more LIKE
patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.
Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.
check for EMAIL format in ROW and return value inside SELECT query
You should be able to use a case
expression:
SELECT (CASE WHEN LOGIN_NAME LIKE '%@%' THEN 'emailAddress' ELSE 'userName' END) as signInType,
LOGIN_NAME AS issuerAssignedId
FROM USER_DATA
FOR JSON AUTO;
Validate email addresses in Mysql
You can use a pure SELECT
to validate Email Addresses:
SELECT * FROM `users` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';
And now for your question of tracking multiple tables, you can use comma seperated table names right?
SELECT * FROM `users`, `customers`, `clients`
WHERE `email` NOT REGEXP "^[a-zA-Z0-9][a-zA-Z0-9.!#$%&'*+-/=?^_`{|}~]*?[a-zA-Z0-9._-]?@[a-zA-Z0-9][a-zA-Z0-9._-]*?[a-zA-Z0-9]?\\.[a-zA-Z]{2,63}$";
Validate email address will be accepted by sp_send_dbmail?
You can try this (there are other ways),
--PARAM:START
DECLARE @EmailList varchar(max);
SET @EmailList = 'Jane Doe <DoeJ@xdomain.com>;info.support@mssqltips.com;.info@mssqltips.com;
info..@mssqltips.com;info@mssqltips.c;info@support@mssqltips.com;info.support@mssql_tips.com;
+info@mssqltips.com;info Support@mssqltips.com;info@mssql tips.com;NULL;22@mssqltips.com;@mssqltips.com';
--PARAM:END
--VALIDATION:START
DECLARE @EmailTable TABLE
(
Email varchar(max),
IsValid bit
);
INSERT INTO @EmailTable(Email, IsValid)
SELECT LTRIM(RTRIM(value))
,CASE WHEN LTRIM(RTRIM(value)) = '' THEN 0
WHEN LTRIM(RTRIM(value)) LIKE '% %' THEN 0
WHEN LTRIM(RTRIM(value)) LIKE ('%["(),:;<>\]%') THEN 0
WHEN SUBSTRING(LTRIM(RTRIM(value)),CHARINDEX('@',LTRIM(RTRIM(value))),LEN(LTRIM(RTRIM(value)))) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 0
WHEN (LEFT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]') OR RIGHT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]')) THEN 0
WHEN (LTRIM(RTRIM(value)) LIKE '%[%' or LTRIM(RTRIM(value)) LIKE '%]%') THEN 0
WHEN LTRIM(RTRIM(value)) LIKE '%@%@%' THEN 0
WHEN LTRIM(RTRIM(value)) NOT LIKE '_%@_%._%' THEN 0
ELSE 1
END
FROM STRING_SPLIT(@EmailList, ';');
--VALIDATION:END
--GET VALID EMAIL
DECLARE @ValidEmailList varchar(max);
SELECT @ValidEmailList = COALESCE(@ValidEmailList + ', ', '') + Email
FROM @EmailTable
WHERE IsValid = 1;
--DO SENDING OF EMAIL USING ValidEmailList
Source: Valid Email Address Check with TSQL
Extract email address from string using tsql
You can search for '@'
in the string. Then you get the string at the LEFT
and RIGHT
side of '@'
. You then want to REVERSE
the LEFT
side and get first occurrence of ' '
then get the SUBSTRING
from there. Then REVERSE
it to get the original form. Same principle apply to the RIGHT
side without doing REVERSE
.
Example string: 'some text someemail@domain.org some text'
LEFT
= 'some text someemail'RIGHT
= '@domain.org some text'- Reverse LEFT = 'liameemos txet emos'
SUBSTRING
up to the first space = 'liameemos'REVERSE
(4) = someemailSUBSTRING
(2) up to the first space = '@domain.org'- Combine 5 and 6 = 'someemail@domain.org'
Your query would be:
;WITH CteEmail(email) AS(
SELECT 'someemail@domain.org' UNION ALL
SELECT 'some text someemail@domain.org some text' UNION ALL
SELECT 'no email'
)
,CteStrings AS(
SELECT
[Left] = LEFT(email, CHARINDEX('@', email, 0) - 1),
Reverse_Left = REVERSE(LEFT(email, CHARINDEX('@', email, 0) - 1)),
[Right] = RIGHT(email, CHARINDEX('@', email, 0) + 1)
FROM CteEmail
WHERE email LIKE '%@%'
)
SELECT *,
REVERSE(
SUBSTRING(Reverse_Left, 0,
CASE
WHEN CHARINDEX(' ', Reverse_Left, 0) = 0 THEN LEN(Reverse_Left) + 1
ELSE CHARINDEX(' ', Reverse_Left, 0)
END
)
)
+
SUBSTRING([Right], 0,
CASE
WHEN CHARINDEX(' ', [Right], 0) = 0 THEN LEN([Right]) + 1
ELSE CHARINDEX(' ', [Right], 0)
END
)
FROM CteStrings
Sample Data:
email
----------------------------------------
someemail@domain.org
some text someemail@domain.org some text
no email
Result
---------------------
someemail@domain.org
someemail@domain.org
Related Topics
Why Can't I Use an Alias in a Delete Statement
Should Every SQL Server Foreign Key Have a Matching Index
How to Execute a Native SQL Script in JPA/Hibernate
Getting SQL Server Cross Database Dependencies
Doctrine Query Builder Using Inner Join with Conditions
Split String into Rows Oracle SQL
Hql: How to Perform an Inner Join on a Subquery
Converting a String to Hex in SQL
Find Last Row in Group by Query-SQL Server
Backup a Single Table with Its Data from a Database in SQL Server 2008
Script Entire Database SQL-Server