Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Regular Expressions Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 8:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 36, Visits: 179
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

Post #1406771
Posted Monday, January 14, 2013 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1406799
Posted Monday, January 14, 2013 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406809
Posted Monday, January 14, 2013 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 36, Visits: 179
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
Post #1406820
Posted Monday, January 14, 2013 10:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1406845
Posted Tuesday, January 15, 2013 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 36, Visits: 179

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.
Post #1407150
Posted Tuesday, January 15, 2013 6:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 36, Visits: 179

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.

Post #1407258
Posted Tuesday, January 15, 2013 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1407273
Posted Tuesday, January 15, 2013 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1407275
Posted Tuesday, January 15, 2013 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1407297
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse