SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Regular Expressions


Regular Expressions

Author
Message
kingdonshel
kingdonshel
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 216
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
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75127 Visits: 40985
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
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64990 Visits: 17979
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.

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)
kingdonshel
kingdonshel
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 216
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
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75127 Visits: 40985
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
kingdonshel
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 216
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
kingdonshel
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 216
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
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41532 Visits: 14413
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
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41532 Visits: 14413
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
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64990 Visits: 17979
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search