Email Validation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Email Validation

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-21 : 07:40:51

I want to check the validation on an email address with LIKE operator.
A valid email address is like email_name@SiteName.com or .org or .net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 07:43:28

http://www.mssqltips.com/sqlservertip/1672/sql-server-function-to-validate-email-addresses/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-21 : 07:46:31

I am looking for a simple way.
Is this correct?
DECLARE @s VARCHAR(50) = 'emailabadi@gmail.com'

SELECT 'Yes'
WHERE @s LIKE '%@%.___'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 08:14:16

quote:
Originally posted by sigmas

I am looking for a simple way.
Is this correct?
DECLARE @s VARCHAR(50) = 'emailabadi@gmail.com'

SELECT 'Yes'
WHERE @s LIKE '%@%.___'

Nope.
See this example


SELECT *
FROM
(
SELECT 'hg@hj@ghg@.com'
)t(val)

WHERE val LIKE '%@%.___'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 08:21:02

also .___ will not include cases like .co.uk
.co.in etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-21 : 08:45:52

What about this?

SELECT *
FROM
(
SELECT 'hg@hj@ghg@.com'
)t(val)
WHERE
(val LIKE '%@%.___'
OR
val LIKE '%@%.__.__'
)
AND val NOT LIKE '%@%@%'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-21 : 11:28:32

You are welcome to read up on the email spec (RFC 5322?). Most people don't realize how crazy real emails can be. You'd have to write some sort of lexical parser to make it truely accurate. Here are some examples that are good for testing:
http://en.wikipedia.org/wiki/Email_address#Valid_email_addresses

Since it's going to be next to impossible to do with a simple LIKE comparison, you'll have to make some tradeoffs.
1. You can make it so that that valid emails will fail ( LIKE '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%')
2. Or you have to go real basic so that invalid emails will pass (LIKE '%@%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources