Question: How can I check email addresses in a SQL Server table?
Answer: I received this question by email again recently. I have answered it on forums and social media, but when I looked through this blog I could not find a clear example. Before showing the query, I want to be precise about the word validate: SQL Server can flag some obvious formatting mistakes. A string pattern cannot prove that a mailbox exists or that a message will arrive.
The original version used a permanent Contacts table in tempdb. For an interview demonstration, I prefer a table variable so the example leaves no object behind. The sample includes plausible addresses, including a plus tag, and several clearly incomplete strings.
DECLARE @Contacts TABLE (EmailAddress nvarchar(320) NOT NULL);
INSERT INTO @Contacts (EmailAddress)
VALUES (N'first@validemail.com'),
(N'second@validemail.com'),
(N'first+tag@validemail.com'),
(N'first@validemail'),
(N'@validemail.com'),
(N'firstvalidemail.com'),
(N'first@@validemail.com'),
(N'first@validemail.');Here is a deliberately modest screening rule. It asks for text before and after one @, a dot with text on either side in the domain, and no literal spaces. It reports every row, so you can inspect both groups instead of silently discarding the rejected ones.
SELECT EmailAddress,
CASE
WHEN EmailAddress LIKE N'%_@_%._%'
AND LEN(EmailAddress) - LEN(REPLACE(EmailAddress, N'@', N'')) = 1
AND EmailAddress NOT LIKE N'% %'
AND RIGHT(EmailAddress, 1) <> N'.'
THEN N'Plausible format'
ELSE N'Needs review'
END AS FormatCheck
FROM @Contacts
ORDER BY EmailAddress;This is a screening example, not a complete specification for email addresses. Microsoft documents that _ in LIKE matches one character, while % matches any sequence. The first pattern alone is too permissive: it can allow extra @ signs, which is why the query counts them separately. It still does not check every allowed address form, DNS, or mailbox delivery.
If this is a real signup flow, validate at the application boundary and confirm the address by sending a message. In SQL Server, use a clearly named format check to find suspicious stored values. That distinction is the answer I would want to hear in an interview. If you use another practical screening rule, share it in the comments; I would be glad to discuss it with credit.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
14 Comments. Leave new
Good one Pinal ?
Just little note: You missed % and + in your validation.
nice
Its not validate with ” econtro;weifwoiaNOIEN3″ string ;)
Can you please explain more?
It doesn’t work for “@fir-st@validemail.com”
with t as (
SELECT ‘@fir-st@validemail.com’ as EmailAddress
)
select * from t
WHERE EmailAddress LIKE ‘%_@__%.__%’
AND PATINDEX(‘%[^a-z,0-9,@,.,_,\-]%’, EmailAddress) = 0
Tu be able to validate e-mails coming from the domain @x.com, I needed to change it a bit :
@email LIKE ‘%_@_%.__%’
AND PATINDEX(‘%[^a-z,0-9,@,.,_,-]%’, @email
yea I also found that, otherwise it works great.
Good but not working for the email having space eg : ank @gmail. com
How can we validate certain characters allowed in the user name portion but not in the domain name portion of the Email address?
E.g. now allowing ‘_’ in the domain portion but allowing it in the User name portion of an email address?
its not working for below email id value :
SELECT ‘@fir@stv@al.comid.email.com’
I think comma in PATHINDEX is a mistake.
It should be
SELECT EmailAddress AS ValidEmail
FROM Contacts
WHERE EmailAddress LIKE ‘%_@__%.__%’
AND PATINDEX(‘%[^a-z0-9@._\-]%’, EmailAddress) = 0
I was unable to use this as intended because the fields I draw email address from often have more than one and have them split up by characters like spaces, semicolons, commons, and other words. My solution ended up being to build a scalar function that pulled the actual email addresses out of the columns so i could drop them into a table variable for working with. Now, once they are outside of that column, your solution is okay, but LIKE ignores some things (like whitespace) for the validation your solution employs.
CREATE FUNCTION [dbo].[IsValidEmail] (@email VARCHAR(200))
RETURNS BIT
AS
/*****************************************************************************************************************************************
Created: 08/06/2020 RBANSAL
Purpose: Check if email is Valid
*****************************************************************************************************************************************/
BEGIN
DECLARE @bitRetVal as Bit = 0
if CHARINDEX(‘ ‘,LTRIM(RTRIM(@email))) = 0 — No embedded spaces
AND LEFT(LTRIM(@email),1) ‘@’ — ‘@’ can’t be the first character of an email address
AND RIGHT(RTRIM(@email),1) ‘.’– ‘.’ can’t be the last character of an email address
AND CHARINDEX(‘.’,@email,CHARINDEX(‘@’,@email)) – CHARINDEX(‘@’,@email) > 1 — There must be a ‘.’ after ‘@’
AND LEN(LTRIM(RTRIM(@email))) – LEN(REPLACE(LTRIM(RTRIM(@email)),’@’,”)) = 1 — Only one ‘@’ sign is allowed
AND CHARINDEX(‘.’,REVERSE(LTRIM(RTRIM(@email)))) >= 3 — Domain name should end with at least 2 character extension
AND (CHARINDEX(‘.@’,@email) = 0 AND CHARINDEX(‘..’,@email) = 0) — can’t have patterns like ‘.@’ and ‘..
BEGIN
IF (@email LIKE ‘%_@__%.__%’ AND PATINDEX(‘%[^a-z,0-9,@,.,_,,\-]%’, @email) = 0)
BEGIN
SET @bitRetVal = 1 — Valid
END
END
RETURN @bitRetVal
END