Email validation in sql server 2008?

Question

How to verify the email is valid or not?

For example:

test@test.com - valid
test@@.com - invalid
test@.com - invalid

Look at http://stackoverflow.com/questions/423606/t-sql-checking-for-email-format — Daniel PP Cabral, Dec 04 '12 at 08:48
I don't think there's a foolproof way of doing this, except to send to it and see if you get an error. Even if you are able to detect if an address is syntactically valid, you can't tell whether the email address exists or whether sending will succeed unless you try it anyway. — Bridge, Dec 04 '12 at 08:54

Mehdi Bugnard · Answer 1 · 2012-12-04T09:29:36.537

Here is an example of creating a table with specific rules for mail with address (regexp). Then an example with sql query pattern (Regexp). With this you should be able to do what you want

Create table with regexp

create table Contacts (
FirstName nvarchar(30),
LastName nvarchar(30),
EmailAddress nvarchar(30) CHECK (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz)', EmailAddress)=1),
USPhoneNo nvarchar(30) CHECK (dbo.RegExMatch('\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]', UsPhoneNo)=1))
INSERT INTO [talend].[dbo].[Contacts]
([FirstName]
,[LastName]
,[EmailAddress]
,[USPhoneNo])
VALUES
('Hallam'
,'Amine'
,'mhallam@talend.com’
,'0129-2090-1092')
,( 'encoremoi'
,'nimportequoi'
,'amine@zichji.org'
,'(122) 190-9090')
GO

Execute request sql with regexp

SELECT [FirstName]
,[LastName]
,[EmailAddress]
,[USPhoneNo]
FROM [talend].[dbo].[Contacts]
where [talend].[dbo].RegExMatch([EmailAddress],'[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz|au)') = 1

Function Code

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExBase
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static int RegExMatch( string matchString , string pattern)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
if (r1.Match(matchString.TrimEnd(null)).Success == true)
{
return 1 ;
}
else
{
return 0 ;
}
}
};

For more explication show here ths tutorial -> http://www.google.ch/url?sa=t&rct=j&q=&esrc=s&source=web&cd=6&ved=0CGkQFjAF&url=http%3A%2F%2Fwww.talendforge.org%2Fbugs%2Ffile_download.php%3Ffile_id%3D4729%26type%3Dbug&ei=f8C9UKTMBNSN4gTo0IHYDg&usg=AFQjCNG-ezRtC9TdcJXuXGl4T8KX4zbUww&sig2=Fpgm5UTYOK4dpsaMfNCCyQ&cad=rja

I hope this help you

Thanks for reply.. this my test data test@test.com valid 'test@test.com valid test@test.com valid t'est@test.com valid hwo to validate it test@test.c invalid @test.com invalid test@ invalid test@@test.com invalid test@test..com invalid test@test. invalid test.com invalid test@test@test.com invalid — murali krishna, Dec 04 '12 at 09:14
There are two possible! My example comes from a given solution linking a program in c # with sql server.Please show this link http://www.google.ch/url?sa=t&rct=j&q=&esrc=s&source=web&cd=6&ved=0CGkQFjAF&url=http%3A%2F%2Fwww.talendforge.org%2Fbugs%2Ffile_download.php%3Ffile_id%3D4729%26type%3Dbug&ei=f8C9UKTMBNSN4gTo0IHYDg&usg=AFQjCNG-ezRtC9TdcJXuXGl4T8KX4zbUww&sig2=Fpgm5UTYOK4dpsaMfNCCyQ&cad=rja If you do not use program in visual studio. You can directly write the function in SQL SERVER. Creating a new stored procedure . A can like this: CREATE PROCEDURE [dbo].[RegExMatch] ........: — Mehdi Bugnard, Dec 04 '12 at 11:00
More informations about this: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx Sorry for my bad english — Mehdi Bugnard, Dec 04 '12 at 11:02

score 1 · Answer 2 · answered Dec 04 '12 at 08:55

1

There is one newer efficient way to do that is to extend .Net functionality for SQL.

Please check details at http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

One ready source code for regex match is at http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008.

Now a simple regex for Email should do the magic for you Hope that helps

answered Dec 04 '12 at 08:55

Milind Thakkar

980
2
13
20

Thanks,can we do it sql server function in DB – murali krishna Dec 04 '12 at 08:58
@muralikrishna: Check the code at both the links provided.You write regex matching C# code and then call that function in SQL. – Milind Thakkar Dec 04 '12 at 09:08
Thank lot i will try let you know – murali krishna Dec 04 '12 at 11:03

score 1 · Answer 3 · answered Dec 04 '12 at 10:12

1

Some years back I wrote a program about Pattern Matching – Email Validation. Hope that may help you.

answered Dec 04 '12 at 10:12

Niladri Biswas

4,153
2
17
24

Email validation in sql server 2008?

3 Answers3

Linked