Email validation using RegEX (SQL)

Question

I am trying to validate email in SQL using RegEX to achieve below criteria.

Create a query (use the operator LIKE) that searches for all the Email Addresses that contain:

Only one symbol “@”
At least one symbol “.”
If there is only one “.” symbol it should be after the symbol “@” (and not before it)
At least 6 characters
The symbols “.” and “@” must not be next to each other
The symbol “.” or “@” must not be at the beginning or end of the address In addition, the Email Address must not contain the following symbols: “=”, “_”, “-“, “+”, “&”, “<”, “>” or “,”.

I tried writing a query and I have considered all the above point except the at least 6 character condition. I am getting the results but all of them is getting displayed as invalid, I am not sure why.

Below is the query that I wrote:

SELECT EmailAddress 
CASE WHEN EmailAddress Like '%^[A-Za-z0-9._%\-+!#$&/=?^|~]+@[A-Za-z0-9.-]+[.][A-Za-z]+$%' THEN 'Valid'
ELSE 'INVALID' END
AS valid_email
FROM Database
Where EmailAddress is not null

Hey @DanielA.White Thanks for the comment. Is there any other way to achieve this in SQL? — Shubham Vajpayee
– Shubham Vajpayee, Commented Jul 10, 2022 at 16:01
Does this answer your question? SQL Email Verification Function using Regex — Andrea M
– Andrea M, Commented Jul 10, 2022 at 16:10
Should [email protected] be treated as valid? Note that RFC2606 tells us that nothing with domain ending .invalid should ever work. How about: [email protected] (could exist but domain is not currently registered)? How about: [email protected] (domain is valid but there is no such local part currently) ? — jhnc
– jhnc, Commented Jul 10, 2022 at 16:15
At least have the terminology right. SQL Server does not support regex, it supports globbong which have very limited capabilities. — David דודו Markovitz
– David דודו Markovitz, Commented Jul 10, 2022 at 16:48

Thom A · Accepted Answer · 2025-02-12 16:42:21Z

1

I did some research and found that the 'like' operator alone doesn't support regular expression; instead, Oracle SQL supports it by using the 'REGEXP_LIKE' keyword.

(^[A-Za-z0-9.%\!#$/?^|~]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$)

I tried your code in this way:

SELECT EmailAddress 
CASE WHEN EmailAddress 
REGEXP_LIKE(^[A-Za-z0-9.%\!#$/?^|~]{1,6}+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$) THEN'Valid'
ELSE 'INVALID' END
AS valid_email
FROM Database
Where EmailAddress is not null;

edited Feb 12 at 16:42

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Feb 12 at 12:39

Sri Tharanya G

193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

julaine Sep 22 at 8:55

best answer, explaining how to write a query with regex instead of working around LIKE not using regex.

julaine Sep 22 at 8:57

wait a second - you mention Oracle SQL. Is this even applicable to T-SQL?

jhnc · Accepted Answer · 2023-08-06 00:06:20Z

0

Standard LIKE doesn't handle regex.

I don't recommend using the following code but it satisfies your criteria:

SELECT
    EmailAddress,
    CASE
        WHEN (
            ( NOT EmailAddress LIKE '%@%@%' )    -- 1
            AND ( EmailAddress LIKE '%.%' )      -- 2
            AND ( EmailAddress LIKE '%@%.%' )    -- 3
            AND ( EmailAddress LIKE '______%' )  -- 4
            AND ( NOT (                          -- 5
                ( EmailAddress LIKE '%@.%' )
                OR ( EmailAddress LIKE '%.@%' )
            ) )
            AND ( NOT (                          -- 6(a)
                ( EmailAddress LIKE '@%' )
                OR ( EmailAddress LIKE '.%' )
                OR ( EmailAddress LIKE '%@' )
                OR ( EmailAddress LIKE '%.' )
            ) )
            AND ( NOT (                          -- 6(b)
                ( EmailAddress LIKE '%=%' )
                OR ( EmailAddress LIKE '%\_%' ESCAPE '\' )
                OR ( EmailAddress LIKE '%-%' )
                OR ( EmailAddress LIKE '%+%' )
                OR ( EmailAddress LIKE '%&%' )
                OR ( EmailAddress LIKE '%<%' )
                OR ( EmailAddress LIKE '%>%' )
                OR ( EmailAddress LIKE '%,%' )
            ) )
        ) THEN 'Valid'
        ELSE 'INVALID'
    END
    AS valid_email
FROM Database
Where EmailAddress IS NOT NULL
;

Notes:

=, _,-, + and & are perfectly valid in the local part
- is valid in the domain part
<, > and , are valid in the local part if properly quoted
this expression does not correctly classify email addresses in general
no expression can determine a priori whether or not an address actually exists, even if it is syntactically valid
I used Database because you did, but you probably meant Database.Table

edited Aug 6, 2023 at 0:06

answered Jul 10, 2022 at 16:47

jhnc

18.7k2 gold badges14 silver badges33 bronze badges

5 Comments

Shubham Vajpayee Over a year ago

Hi @jhnc, Unfortunately, I am not able to upvote the answer but thank you so much. It worked! I really appreciate your help :)

jhnc Over a year ago

@ShubhamVajpayee you really shouldn't do email validation this way. it excludes many perfectly-valid email addresses

Shubham Vajpayee Over a year ago

It would be really helpful if you could tell me the other ways to achieve this as I am not aware of any other ways :(

jhnc Over a year ago

@ShubhamVajpayee depends what "achieve this" means. my code does what you requested but does not validate email correctly. The syntax of a valid email address is very complicated. The only reliable way to check validity is to do a call-out (eg. send email to the address requesting confirmation and update your table when you receive the reply - consider e-commerce site registration). Even that is time-limited: addresses that exist when you test can be deleted in future.

jhnc Over a year ago

@ShubhamVajpayee serious tongue-in-cheek

Luuk · Accepted Answer · 2025-08-22 17:39:55Z

Because the (current) other answers says that regular expression cannot be used in Microsoft SQL Server, you can do it without those like this:

WITH sampleaddresses as (
  SELECT '[email protected]' as email UNION ALL
  SELECT '@example.com' UNION ALL
  SELECT '[email protected]' UNION ALL
  SELECT 'invalid@a+b.com' UNION ALL
  SELECT 'invalid2@' UNION ALL
  SELECT 'a.b@c'
),
invalidchars as (
  SELECT value FROM STRING_SPLIT('=|_|-|+|&|<|>|,','|')
)
SELECT DISTINCT email 
FROM sampleaddresses
CROSS APPLY invalidchars
WHERE CHARINDEX(value, email)<>0       -- no invalid chars
   OR LEN(email)-LEN(REPLACE(email,'@','')) <> 1   -- not more than one '@'
   OR LEN(email)-LEN(REPLACE(email,'.','')) < 1   -- al least one '.'
   OR CHARINDEX('.',SUBSTRING(email,charindex('@',email),100))=0  -- '.' after '@'
   OR SUBSTRING(email,1,1)  in('@','.') --  starting with '@' or '.'
   Or SUBSTRING(email,LEN(email),1)  in('@','.') --  ending with '@' or '.'

Example: DBFIDDLE

This SQL has all invalid options in the WHERE clause, to detect an invalid item.

The sampleaddresses` can be expanded to test other situations. (Sorry for my lack of providing testcases for all possible bad email addresses....)

Collectives™ on Stack Overflow

Email validation using RegEX (SQL)

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related