Solved

regular expression to check email in sql

Posted on 2008-06-12
10
1,461 Views
Last Modified: 2008-06-18
I use a pretty good function in js to validate my email It uses a regular expresion:

function validateEmail(fieldValue) {
     return (/^[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$/).test(fieldValue);
}

How can I implement a function in SQL to redenr the same result?

I have tried some functions that just do not cut it
0
Comment
Question by:robrodp
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 21772316
You can not do this directly in SQL.  You have to do a lot more to get this to work.

I found a solution here and implemented it and it works really well (but takes a lot more work to to get it implemented).

http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx

If you can continue to use JS and/or ASP code to do this and you do not have to have it in SQL I would say maybe keep it in ASP code for the amount of work you would need to do to get it to work in SQL.
0
 

Author Comment

by:robrodp
ID: 21772829
Hi...

Too complicated....

I need a simple tool that will efectively check my emails. Funcions I have used leave much to be desired

Any ideas?
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 21775143
What are the issues you have had with previous functions?

Is there a particular reason you want the regex used on the SQL Server side?  The expert above is right that other languages (e.g. ASP) usually have better implementation and support for regex.  It would seem better to validate the email before inserting it in the DB and have the server language do this work.

There are more complicated expressions to better match a valid email address.  However they can be longer than needed for most and so it is best to know the general addresses you will allow.  Also if you disappointment is from email addresses that aren't real (but seem valid) then no regex will fix this.

Let me know if you have a question or need more info.

bol
0
 
LVL 10

Expert Comment

by:bluefezteam
ID: 21777498
Here is a simple to use VB.NET regexp to test for valid emails which returns a boolean value
    Function validEmail(ByVal strEmail As String) As Boolean

        Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"

        Dim emailAddressMatch As Match = Regex.Match(strEmail, pattern)

        If emailAddressMatch.Success Then

            Return True

        Else

            Return False

        End If

    End Function

Open in new window

0
 

Author Comment

by:robrodp
ID: 21778138
Hi guys

Of course I do not want a function to seed out that are not real (as much as I would like to)

I just want to validate in SQL as effectively as I do with the js function I have.  bluefezteam has a function but it is not an SQL solution or is it?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 10

Expert Comment

by:bluefezteam
ID: 21779616
no, its a vb.net solution

if you want to write the regular expression in T-SQL you have a lot of work in store. examples of regex in SQL server can be found here:

http://www.sqlteam.com/article/regular-expressions-in-t-sql
0
 
LVL 54

Accepted Solution

by:
b0lsc0tt earned 500 total points
ID: 21781125
T-SQL is not going to work well for this.  You need to do the validation with the server script (i.e. ASP).  Once the data is in the database it is really past the time to validate it.  Validation is better done by the server and its script than wasting the database to try to do it.  This is most likely why the support for regex in SQL (no matter what the database is) is poor and limited.

I am glad you are not going to rely on the Javascript validation but ASP is where you should be doing this.  The code in the snippet below will do this in ASP.  You would put it before inserting the info in the database.

bol
Dim myRegExp, FoundMatch

Set myRegExp = New RegExp

myRegExp.Pattern = "^[a-zA-Z0-9][\w.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z.]*[a-zA-Z]$"

FoundMatch = myRegExp.Test(SubjectString)

If FoundMatch = False then

    ' not a match, reject or do what you want

End if

Open in new window

0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 21815659
I'm glad I could help.  I am curious about the B grade though.  Did you still have a question or need some help?  Let us know if that is the case and we can provide more info.  Of course we can't make TSQL or your database use an expression for validation but can certainly provide more info if you need it.  It is best to always have a chance to earn the A grade or know why it wasn't.

If there was a mistake in the grade or you have a question about what they mean here then let me know.

Thanks for the fun question.

bol
0
 

Author Comment

by:robrodp
ID: 21815954
Hi...

The grade is because my concern is how to do it from the sql manager. Your answer if for asp, which solves the problem but I am still looking for an sql solution
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 21816105
Thanks for the response and explanation.  I thought I had explained why it wasn't available or a good place to do the validation.  Sorry if it wasn't real clear or was overlooked.  I doubt SQL will ever provide it in any database but if it were available it would still be a poor spot to do validation.

Thanks for the fun question and especially the response on the grade.  I am glad I could help even if it wasn't what you thought you could get and do.

bol
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now