InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 23:32:29 GMT20http://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxI 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"][size="2"][color="blue"]using [/color][color="black"]System[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlClient[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlTypes[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]Microsoft.SqlServer.Server[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Text.RegularExpressions[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Collections[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Net.Mail[/color][color="gray"];[/color][color="blue"]public [/color][color="black"]partial [/color][color="blue"]class [/color][color="black"]UserDefinedFunctions{    [/color][color="blue"]private static readonly [/color][color="black"]Regex re [/color][color="blue"]= new [/color][color="black"]Regex[/color][color="gray"]([/color][color="darkred"]"^([0-9a-zA-Z]([-.\\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\\w]*[0-9a-zA-Z]\\.)+[a-zA-Z]{2,9})$"[/color][color="gray"]);    [/color][color="black"][Microsoft.SqlServer.Server.SqlFunction]    [/color][color="blue"]public static [/color][color="black"]SqlBoolean IsValidEmailRegEx[/color][color="gray"]([/color][color="black"]SqlString email[/color][color="gray"])    [/color][color="black"]{        [/color][color="blue"]return [/color][color="black"]re.IsMatch[/color][color="gray"]([/color][color="black"]email.ToString[/color][color="gray"]());    [/color][color="black"]}    [SqlFunction(FillRowMethodName = "EmailIsValidRegEx",        TableDefinition = "EmailIsValid bit")]    [/color][color="blue"]public static [/color][color="black"]IEnumerable EmailValidityRegEx[/color][color="gray"]([/color][color="black"]SqlString emailAddress[/color][color="gray"])    [/color][color="black"]{        ArrayList EmailCollection [/color][color="blue"]= new [/color][color="black"]ArrayList[/color][color="gray"]();        [/color][color="black"]EmailCollection.Add[/color][color="gray"]([/color][color="black"]emailAddress[/color][color="gray"]);        [/color][color="blue"]return [/color][color="black"]EmailCollection[/color][color="gray"];    [/color][color="black"]}    [/color][color="green"]//FillRow method. The method name has been specified above as     //a SqlFunction attribute property    [/color][color="blue"]public static void [/color][color="black"]EmailIsValidRegEx[/color][color="gray"]([/color][color="blue"]object [/color][color="black"]emailAddress[/color][color="gray"],                                    [/color][color="black"]out SqlBoolean emailIsValid[/color][color="gray"])    [/color][color="black"]{        emailIsValid [/color][color="blue"]= [/color][color="black"]re.IsMatch[/color][color="gray"]([/color][color="black"]emailAddress.ToString[/color][color="gray"]());    [/color][color="black"]}    }[/color][color="gray"];[/color][/size][/font][font="Courier New"][size="2"][color="blue"]using [/color][color="black"]System[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlClient[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlTypes[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]Microsoft.SqlServer.Server[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Collections[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Net.Mail[/color][color="gray"];[/color][color="blue"]public [/color][color="black"]partial [/color][color="blue"]class [/color][color="black"]UserDefinedFunctions{    [Microsoft.SqlServer.Server.SqlFunction]    [/color][color="blue"]public static [/color][color="black"]SqlBoolean IsValidEmailSystemNet[/color][color="gray"]([/color][color="black"]SqlString email[/color][color="gray"])    [/color][color="black"]{        [/color][color="blue"]try        [/color][color="black"]{            System.Net.Mail.MailAddress ma [/color][color="blue"]= new [/color][color="black"]System.Net.Mail.MailAddress[/color][color="gray"]([/color][color="black"]email.ToString[/color][color="gray"]());            [/color][color="blue"]return [/color][color="gray"]([/color][color="black"]SqlBoolean[/color][color="gray"])[/color][color="blue"]true[/color][color="gray"];        [/color][color="black"]}        [/color][color="blue"]catch        [/color][color="black"]{            [/color][color="blue"]return [/color][color="gray"]([/color][color="black"]SqlBoolean[/color][color="gray"])[/color][color="blue"]false[/color][color="gray"];        [/color][color="black"]}    }    [SqlFunction(FillRowMethodName = "EmailIsValidSystemNet",        TableDefinition = "EmailIsValid bit")]    [/color][color="blue"]public static [/color][color="black"]IEnumerable EmailValiditySystemNet[/color][color="gray"]([/color][color="black"]SqlString emailAddress[/color][color="gray"])    [/color][color="black"]{        ArrayList EmailCollection [/color][color="blue"]= new [/color][color="black"]ArrayList[/color][color="gray"]();        [/color][color="black"]EmailCollection.Add[/color][color="gray"]([/color][color="black"]emailAddress[/color][color="gray"]);        [/color][color="blue"]return [/color][color="black"]EmailCollection[/color][color="gray"];    [/color][color="black"]}    [/color][color="green"]//FillRow method. The method name has been specified above as     //a SqlFunction attribute property    [/color][color="blue"]public static void [/color][color="black"]EmailIsValidSystemNet[/color][color="gray"]([/color][color="blue"]object [/color][color="black"]emailAddress[/color][color="gray"],                                    [/color][color="black"]out SqlBoolean emailIsValid[/color][color="gray"])    [/color][color="black"]{        [/color][color="blue"]try        [/color][color="black"]{            MailAddress ma [/color][color="blue"]= new [/color][color="black"]MailAddress[/color][color="gray"]([/color][color="black"]emailAddress.ToString[/color][color="gray"]());            [/color][color="black"]emailIsValid [/color][color="blue"]= [/color][color="gray"]([/color][color="black"]SqlBoolean[/color][color="gray"])[/color][color="blue"]true[/color][color="gray"];        [/color][color="black"]}        [/color][color="blue"]catch        [/color][color="black"]{            emailIsValid [/color][color="blue"]= [/color][color="gray"]([/color][color="black"]SqlBoolean[/color][color="gray"])[/color][color="blue"]false[/color][color="gray"];        [/color][color="black"]}    }}[/color][color="gray"];[/color][/size][/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:[code="plain"]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.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.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.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.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.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.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/code][code="sql"]USE test;SET STATISTICS TIME OFF;IF OBJECT_ID(N'tempdb..#MySampleEmails') IS NOT NULL DROP TABLE #MySampleEmails;GOWITH 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;GOIF OBJECT_ID(N'tempdb..#deleteme2') IS NOT NULL DROP TABLE #deleteme2;GOIF OBJECT_ID(N'tempdb..#deleteme3') IS NOT NULL DROP TABLE #deleteme3;GOIF OBJECT_ID(N'tempdb..#deleteme4') IS NOT NULL DROP TABLE #deleteme4;GODECLARE @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 IsValidEmailINTO #deleteme1FROM #MySampleEmailsOPTION (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 IsValidEmailINTO #deleteme2FROM #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.EmailIsValidINTO #deleteme4FROM #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.EmailIsValidINTO #deleteme3FROM #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;GOIF OBJECT_ID(N'tempdb..#deleteme2') IS NOT NULL DROP TABLE #deleteme2;GOIF OBJECT_ID(N'tempdb..#deleteme3') IS NOT NULL DROP TABLE #deleteme3;GOIF OBJECT_ID(N'tempdb..#deleteme4') IS NOT NULL DROP TABLE #deleteme4;GOIF OBJECT_ID(N'tempdb..#MySampleEmails') IS NOT NULL DROP TABLE #MySampleEmails;GO[/code]Tue, 15 Jan 2013 21:05:58 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxLowell 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.[code](1000 row(s) affected)--------------------------------------------------------------------------------CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE--------------------------------------------------------------------------------DBCC execution completed. If DBCC printed error messages, contact your system administrator.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.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.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.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/code]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 .[code]select d1.RandomCode, d1.IsValidEmail, d2.IsValidEmail from #deleteme1 d1left join #Deleteme2 d2 on d1.Randomcode = d2.randomCode where d1.IsValidEmail <> isnull(d2.IsValidEmail, 2)select d1.RandomCode, d1.IsValidEmail, d2.IsValidEmail from #deleteme2 d2left join #Deleteme1 d1 on d1.Randomcode = d2.randomCode where d2.IsValidEmail <> isnull(d1.IsValidEmail, 2)[/code]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. ;-)Tue, 15 Jan 2013 12:25:09 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxok 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:[code]--------------------------------------------------------------------------------CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE--------------------------------------------------------------------------------DBCC execution completed. If DBCC printed error messages, contact your system administrator.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.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.DBCC execution completed. If DBCC printed error messages, contact your system administrator.========== Using RegEx Function ==========--Gave up after Eight Plus minutes[/code]this is what i used for testing: I generate some random data, that sometimes matches the expected format with @ and .com in it.[code]WITHcteBase 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 #MySampleEmailsSET 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 #MySampleEmailsSET 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 #deleteme1drop table #deleteme2drop table #MySampleEmails[/code]Tue, 15 Jan 2013 10:54:53 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx[quote][b]opc.three (1/15/2013)[/b][hr]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.[/quote]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. Tue, 15 Jan 2013 08:32:01 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx[quote][b]kingdonshel (1/15/2013)[/b][hr]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.[/quote]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.Tue, 15 Jan 2013 07:43:59 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxLeveraging 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.Tue, 15 Jan 2013 07:34:26 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxI 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.Tue, 15 Jan 2013 06:59:52 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxMany 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.Tue, 15 Jan 2013 04:18:12 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxOK 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[b][url=http://www.stormrage.com/SQLStuff/CLR_RegEx_Example_2008.zip]CLR_RegEx_Example_2008.zip[/url][/b]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:[code]ALTER DATABASE SandBox SET TRUSTWORTHY ON;[/code][img]http://www.stormrage.com/SQLStuff/clr_Project_Change_Database.png[/img]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:[code]select dbo.IsValidEmail1('lowell.someplace'), dbo.IsValidEmail2('lowell.someplace')select dbo.IsValidEmail1('lowell@somplace.com'), dbo.IsValidEmail2('lowell@somplace.com')[/code]Mon, 14 Jan 2013 10:37:22 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxHi 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.ThanksMon, 14 Jan 2013 09:52:55 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxThat 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).[code]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); }[/code]Mon, 14 Jan 2013 09:27:58 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxthis 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.[code="plain"]'--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[/code]Mon, 14 Jan 2013 09:17:03 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxI 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 appreciatedMon, 14 Jan 2013 08:38:56 GMTkingdonshel