Regular Expressions

Question

Regular Expressions

kingdonshel

SSCommitted

Points: 1704
More actions
January 14, 2013 at 8:38 am

#272739

I am looking into writing a regular expression for validating emails. I am an absolute novice at this, could some explain what are regular expressions, with an example of how to implement it when validating emails using a CLR function.
links to clear good exmples and explanations would be very much appreciated

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is the code for the CLR i use; It leverages the the .Net Mail object, which has some pretty expansive, built in rules for handling email addresses.

If you are just starting out, I could slap this into an example project with that code in it already if you think you need a bit more help.

'--requires Imports System.Net

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Function IsValidEmail(ByVal email As SqlString) As SqlInt32

Try

Dim ma As New System.Net.Mail.MailAddress(email)

Return New SqlInt32(1)

Catch

Return New SqlInt32(0)

End Try

End Function

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

That is a pretty good idea Lowell. I have rolled my own IsValidEmail using regex but I know it is not as robust as the .NET MailAddress object. I have been using my own for a long time and have not yet encountered a legitimate email address that my code failed on but there are most likely a number of things I didn't consider.

In case somebody wants to see an example of using a roll your own regex here is the method I have (this is not a CLR method but the conversion to CLR would be very simple).

public static bool IsValidEmail(string email)

{

Regex re = new Regex(@"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$");

return re.IsMatch(email);

}

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kingdonshel SSCommitted Points: 1704 More actions · Answer 3

Hi Lowel I don't quite understand your code it seems to be in VB as well. what I am trying to do is to create a User defined function CLR using C# in Visual studio 2008 which should then be deployed to SQL Server 2008.

I should then be able to select the function and use it to parse emails.

I am not saying your code is wrong, as newbiw at CLR and Regular expression it would be more beneficial if you could walk me through the creation of the CLR in VS then possible atest example of it working in SQL MS.

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 4

OK here's a starter kit for you in C# for SQL2008.

the usual caveats about downloading strangers code and all that applies, obviously.

don't deploy code you don't fully understand, and only deploy this to your developer testing server/database until you are sure of it.

here is a 2008 Project, all zipped up with both my example and my friend Sean Lange's example, named IsValidEmail1 and isValidEmail2

CLR_RegEx_Example_2008.zip

you need to change the database connection of the project to point to your own server/database: mine was dev223/SandBox:

if you get a deployment error, it might be becasue the database needs to be set to trustworthy:

ALTER DATABASE SandBox SET TRUSTWORTHY ON;

after you change that, you want to build the project , then deploy the project from the VS2008 Build...Build SQLServerProject2 menu item.

testing a scalar function like this is a simple TSQL command:

select

dbo.IsValidEmail1('lowell.someplace'),

dbo.IsValidEmail2('lowell.someplace')

select

dbo.IsValidEmail1('lowell@somplace.com'),

dbo.IsValidEmail2('lowell@somplace.com')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kingdonshel SSCommitted Points: 1704 More actions · Answer 5

Many thanks for your help Howell also to Sean Lange, eveerything seems a bit simpler now and understandable.

I have to admit with some of my testing so far Sean's example work better however I both solutions do work. I like the built in System.Net.Mail.MailAddress functionality it makes things easier in some cases.

This has been my first succesful attempt in getting CLR to work so many thanks, now I need to improve on my C# so I can do more with CLR functions.

If you guys have any more working examples of CLR functions with C# on SQL server feel free to send them through.

Thanks.

kingdonshel SSCommitted Points: 1704 More actions · Answer 6

I was wondering has anyone ever written a regular expression thatreurns a boolean for wether a phone number is a valid phone number in the US or UK or wether its a mobile number or not.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Leveraging the validation built into the System.Net.Mail.MailAddress constructor is clever and I did not have to set trustworthy on to use it. The RegEx version is nice too. I wonder which one is faster.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

kingdonshel (1/15/2013)
I was wondering has anyone ever written a regular expression thatreurns a boolean for wether a phone number is a valid phone number in the US or UK or wether its a mobile number or not.

A technique to validate the number of digits in a string is trivial to do with a tally table. I am not sure if area codes are still standard (0 or 1 in middle position used to be a standard) or whether phone exchanges can tell you whether a number is for a mobile or land line. If there is a standard I am sure there is a regex posted on the net somewhere you could pickup and use, but it would be risky in case the standards changed. There are third party tools that do this but they work from a local database which you have to keep up to date to do the validation, and of course that would be $$$ out of pocket. I had a good experience using MelissaData products for address validation and they offer phone number validation tools as well.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

opc.three (1/15/2013)
Leveraging the validation built into the System.Net.Mail.MailAddress constructor is clever and I did not have to set trustworthy on to use it. The RegEx version is nice too. I wonder which one is faster.

I will try to put together a test later today. At a guess I would think they would be pretty close if all the data is valid. When the emails are invalid I think the regex version will start to sneak ahead because of the additional overhead of throwing exceptions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 10

ok i'm not sure if this is doing a valid performance test or not;

i had initially thought that creating an object would be slower than regex, but that's not what i'm seeing. On my Dev machine with no load on it at all, but limited to a max memory of 1024 Meg, i'm getting results for 1 Million random rows of data in about 35 seconds for the .Net item, but the Regex seems to go for more than eight minutes; i wonder if it's because regex is not System.IDisposable?

does anyone see similar results, or am i missing something obvious?

results:

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 526 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using Mail Object Function ==========

(1000000 row(s) affected)

SQL Server Execution Times:

CPU time = 23665 ms, elapsed time = 30350 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using RegEx Function ==========

--Gave up after Eight Plus minutes

this is what i used for testing:

I generate some random data, that sometimes matches the expected format with @ and .com in it.

WITH

cteBase AS

( --=== Gen enough 8 character random codes

SELECT TOP 1000000 LEFT(NEWID(),8) AS RandomCode

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

)

,

cteManip AS

( --=== Number the codes so we can select unique random codes

SELECT RandomCode

+ CASE WHEN RandomCode LIKE '[A-Z]%' THEN '@' ELSE '' END

+ RandomCode

+ CASE WHEN RandomCode LIKE '[A-Z]%' THEN '.com' ELSE '' END

AS RandomCode

FROM cteBase

) --=== Select 1 million random codes

SELECT identity(int,1,1) as ID,RandomCode

INTO #MySampleEmails

FROM cteManip

DECLARE @HOLDER VARCHAR(20);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== BASELINE ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER = COUNT(*)

FROM #MySampleEmails;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using Mail Object Function ==========';

SET STATISTICS TIME ON;

SELECT *,dbo.IsValidEmail1(RandomCode) As IsValidEmail

into #deleteme1

FROM #MySampleEmails

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using RegEx Function ==========';

SET STATISTICS TIME ON;

SELECT *,dbo.IsValidEmail2(RandomCode) As IsValidEmail

into #deleteme2

FROM #MySampleEmails

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

drop table #deleteme1

drop table #deleteme2

drop table #MySampleEmails

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Lowell I dropped the million to pull only 1,000 and it is painfully obvious that the MailAddress method blows the doors off the regex version for performance.

Here is the output.

(1000 row(s) affected)

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using Mail Object Function ==========

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 74 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using RegEx Function ==========

SQL Server Execution Times:

CPU time = 7379 ms, elapsed time = 7501 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Until yesterday I had never had the regex code that I posted used in a CLR but I can't think of anything that would help it perform faster. Seems to me that the MailAddress version will perform better in addition to being more thoroughly tested. Although I did run this a lot of times with the following query .

select d1.RandomCode, d1.IsValidEmail, d2.IsValidEmail

from #deleteme1 d1

left join #Deleteme2 d2 on d1.Randomcode = d2.randomCode

where d1.IsValidEmail <> isnull(d2.IsValidEmail, 2)

select d1.RandomCode, d1.IsValidEmail, d2.IsValidEmail

from #deleteme2 d2

left join #Deleteme1 d1 on d1.Randomcode = d2.randomCode

where d2.IsValidEmail <> isnull(d1.IsValidEmail, 2)

I was wondering if I would find any of these random codes that returned different results but I didn't seem to have any luck finding any. I wonder if turning them into table valued CLR's would help performance over the scalar udf. I am swamped right now but this sounds like a fun project. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

I was really surprised at how poorly the RegEx did compared to MailAddress. I am now wondering what MailAddress uses to determine validity.

Here are a couple TVFs to go along with the SVFs:

[font="Courier New"]using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using System.Collections;

using System.Net.Mail;

public partial class UserDefinedFunctions

{

private static readonly Regex re = new Regex("^([0-9a-zA-Z]([-.\\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\\w]*[0-9a-zA-Z]\\.)+[a-zA-Z]{2,9})$");

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean IsValidEmailRegEx(SqlString email)

{

return re.IsMatch(email.ToString());

}

[SqlFunction(FillRowMethodName = "EmailIsValidRegEx",

TableDefinition = "EmailIsValid bit")]

public static IEnumerable EmailValidityRegEx(SqlString emailAddress)

{

ArrayList EmailCollection = new ArrayList();

EmailCollection.Add(emailAddress);

return EmailCollection;

}

//FillRow method. The method name has been specified above as

//a SqlFunction attribute property

public static void EmailIsValidRegEx(object emailAddress,

out SqlBoolean emailIsValid)

{

emailIsValid = re.IsMatch(emailAddress.ToString());

}

};

[/font]

[font="Courier New"]using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Net.Mail;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean IsValidEmailSystemNet(SqlString email)

{

try

{

System.Net.Mail.MailAddress ma = new System.Net.Mail.MailAddress(email.ToString());

return (SqlBoolean)true;

}

catch

{

return (SqlBoolean)false;

}

[SqlFunction(FillRowMethodName = "EmailIsValidSystemNet",

TableDefinition = "EmailIsValid bit")]

public static IEnumerable EmailValiditySystemNet(SqlString emailAddress)

{

ArrayList EmailCollection = new ArrayList();

EmailCollection.Add(emailAddress);

return EmailCollection;

}

//FillRow method. The method name has been specified above as

//a SqlFunction attribute property

public static void EmailIsValidSystemNet(object emailAddress,

out SqlBoolean emailIsValid)

{

try

{

MailAddress ma = new MailAddress(emailAddress.ToString());

emailIsValid = (SqlBoolean)true;

}

catch

{

emailIsValid = (SqlBoolean)false;

}

};

[/font]

On my lowly 32-bit 2.4 GHz Intel Core 2 Duo laptop w/4GB RAM the MailAddress SVF took the prize although I wonder if on a machine with more CPUs if there would be a tipping where the TVF might finish in a shorter amount of time:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using Mail Object Function ==========

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 61 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using RegEx Function ==========

SQL Server Execution Times:

CPU time = 7145 ms, elapsed time = 7437 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using Table-Valued Mail Object Function ==========

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 80 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== Using Table-Valued RegEx Function ==========

SQL Server Execution Times:

CPU time = 7207 ms, elapsed time = 7429 ms.

(1000 row(s) affected)

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

USE test;

SET STATISTICS TIME OFF;

IF OBJECT_ID(N'tempdb..#MySampleEmails') IS NOT NULL

DROP TABLE #MySampleEmails;

GO

WITH cteBase

AS (

--=== Gen enough 8 character random codes

SELECT TOP 1000

LEFT(NEWID(), 8) AS RandomCode

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

),

cteManip

AS (

--=== Number the codes so we can select unique random codes

SELECT RandomCode + CASE WHEN RandomCode LIKE '[A-Z]%' THEN '@'

ELSE ''

END + RandomCode + CASE WHEN RandomCode LIKE '[A-Z]%' THEN '.com'

ELSE ''

END AS RandomCode

FROM cteBase

)

--=== Select 1 million random codes

SELECT IDENTITY( INT,1,1 ) AS ID,

RandomCode

INTO #MySampleEmails

FROM cteManip;

---------------------------------------

IF OBJECT_ID(N'tempdb..#deleteme1') IS NOT NULL

DROP TABLE #deleteme1;

GO

IF OBJECT_ID(N'tempdb..#deleteme2') IS NOT NULL

DROP TABLE #deleteme2;

GO

IF OBJECT_ID(N'tempdb..#deleteme3') IS NOT NULL

DROP TABLE #deleteme3;

GO

IF OBJECT_ID(N'tempdb..#deleteme4') IS NOT NULL

DROP TABLE #deleteme4;

GO

DECLARE @HOLDER VARCHAR(20);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== BASELINE ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER = COUNT(*)

FROM #MySampleEmails;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=', 80);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using Mail Object Function ==========';

SET STATISTICS TIME ON;

SELECT *,

dbo.IsValidEmailSystemNet(RandomCode) AS IsValidEmail

INTO #deleteme1

FROM #MySampleEmails

OPTION (MAXDOP 0);

SET STATISTICS TIME OFF;

PRINT REPLICATE('=', 80);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using RegEx Function ==========';

SET STATISTICS TIME ON;

SELECT *,

dbo.IsValidEmailRegEx(RandomCode) AS IsValidEmail

INTO #deleteme2

FROM #MySampleEmails;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=', 80);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using Table-Valued Mail Object Function ==========';

SET STATISTICS TIME ON;

SELECT mse.ID,

mse.RandomCode,

x.EmailIsValid

INTO #deleteme4

FROM #MySampleEmails mse

CROSS APPLY dbo.EmailValiditySystemNet(mse.RandomCode) x;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=', 80);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== Using Table-Valued RegEx Function ==========';

SET STATISTICS TIME ON;

SELECT mse.ID,

mse.RandomCode,

x.EmailIsValid

INTO #deleteme3

FROM #MySampleEmails mse

CROSS APPLY dbo.EmailValidityRegEx(mse.RandomCode) x;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=', 80);

PRINT REPLICATE('-', 80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-', 80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

-----------------------------------------------------------------------------

IF OBJECT_ID(N'tempdb..#deleteme1') IS NOT NULL

DROP TABLE #deleteme1;

GO

IF OBJECT_ID(N'tempdb..#deleteme2') IS NOT NULL

DROP TABLE #deleteme2;

GO

IF OBJECT_ID(N'tempdb..#deleteme3') IS NOT NULL

DROP TABLE #deleteme3;

GO

IF OBJECT_ID(N'tempdb..#deleteme4') IS NOT NULL

DROP TABLE #deleteme4;

GO

IF OBJECT_ID(N'tempdb..#MySampleEmails') IS NOT NULL

DROP TABLE #MySampleEmails;

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato