Issue with Email address validation in Sql server

Question

I am using the following query

SELECT * 
FROM people 
WHERE email NOT LIKE '%_@__%.__%'

referred from the link to validate the email address.

The query is working fine in all scenarios except when the email is invalid due to a space. For example, the query is failing for the following invalid email address value. @ .

Can we find the column with space using a Like statement?

There's usually a more appropriate layer at which to do email validation than in the database. And using pattern matching (whether simple or regex) for email validation is generally flawed. Accept anything with an `@` and if you want any more assurance, the only way to be sure the email address is valid is to attempt to use it. — Damien_The_Unbeliever, Mar 07 '14 at 07:08
So, the `mysql`, `sqlite` and `sql-server-2008` tags were *all* wrongly applied then? — Damien_The_Unbeliever, Mar 07 '14 at 07:27

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

Email validation is a hairy exercise at best. At a minimum, I'd validate in a stored procedure written in a 3rd language. As your linked answer notes:

Validating e-mail addresses in code is virtually impossible.

edited May 23 '17 at 12:20

Community

1
1

answered Mar 07 '14 at 07:07

hd1

33,938
5
80
91

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%' query is working fine in almost all cases except when a space is present. I am thinking if I can validate space then I will use multiple queries to validate email address, which will cover almost all scenarios. – user3306829 Mar 07 '14 at 07:27
why don't you use `trim` then later apply your query ? – vhadalgi Mar 07 '14 at 07:38
I need to check whether the column has any space. Trim will not serve the purpose. – user3306829 Mar 07 '14 at 08:02
This should really be a comment, on nth thought. Sorry about that, users. – hd1 Mar 07 '14 at 09:01

score 0 · Answer 2 · answered Sep 02 '14 at 18:21

0

You can use the LEN() function and Replace() functions:

Select
  Id,
  Email
From
  MyTable
Where
  LEN(Email) <> LEN(REPLACE(Email,' ', ''))

See the SqlFiddle here

answered Sep 02 '14 at 18:21

Josh Jay

1,240
2
14
26

Issue with Email address validation in Sql server

2 Answers2