SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Email Validation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 05/21/2013 :  07:40:51  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/21/2013 :  07:43:28  Show Profile  Reply with Quote
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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 05/21/2013 :  07:46:31  Show Profile  Reply with Quote
I am looking for a simple way.
Is this correct?
DECLARE @s VARCHAR(50) = 'emailabadi@gmail.com'

SELECT 'Yes'
WHERE @s LIKE '%@%.___'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  08:14:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  08:21:02  Show Profile  Reply with Quote
also .___ will not include cases like .co.uk
.co.in etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 05/21/2013 :  08:45:52  Show Profile  Reply with Quote
What about this?


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

Edited by - sigmas on 05/21/2013 08:46:49
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 05/21/2013 :  11:28:32  Show Profile  Reply with Quote
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 '%@%')

Edited by - Lamprey on 05/21/2013 11:35:17
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000