T-Sql: Checking for Email Format

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'

  1. LEFT = 'some text someemail'
  2. RIGHT = '@domain.org some text'
  3. Reverse LEFT = 'liameemos txet emos'
  4. SUBSTRING up to the first space = 'liameemos'
  5. REVERSE(4) = someemail
  6. SUBSTRING (2) up to the first space = '@domain.org'
  7. 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



Leave a reply



Submit