Question:Â How to Validate Email Address in SQL Server?
Answer: I just received this question in an email a week ago. The matter of fact, I have answered this email quite a many times before on various forums and on social media. However, when I searched this blog, I could not find a post related to this topic. Hence, I decided to write this email.
Let us create a column called EmailAddress in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.
USE tempdb GO CREATE TABLE Contacts (EmailAddress VARCHAR(100)) INSERT INTO Contacts (EmailAddress) SELECT 'first@validemail.com' UNION ALL SELECT 'first@validemail' UNION ALL SELECT '@validemail.com' UNION ALL SELECT 'second@validemail.com' UNION ALL SELECT 'firstvalidemail.com' GO
Next we will run following script which will only select the valid email address:
SELECT EmailAddress AS ValidEmail
FROM Contacts
WHERE EmailAddress LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0
GOWe can also use NOT condition in the WHERE clause and select all the invalid emails as well.
SELECT EmailAddress AS NotValidEmail
FROM Contacts
WHERE NOT EmailAddress LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0
GOI guess, that’s it. Let me know if you are using any other trick to find out valid and invalid email addresses in SQL Server. Please post a comment and I will be happy to post it here with due credit.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
Good one Pinal ?
Just little note: You missed % and + in your validation.
nice
Its not validate with ” econtro;weifwoiaNOIEN3″ string ;)
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