Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1501
  • Last Modified:

regular expression to check email in sql

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
robrodp
Asked:
robrodp
  • 4
  • 3
  • 2
  • +1
1 Solution
 
brad2575Commented:
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
 
robrodpAuthor Commented:
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
 
b0lsc0ttIT ManagerCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
bluefezteamCommented:
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
 
robrodpAuthor Commented:
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
 
bluefezteamCommented:
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
 
b0lsc0ttIT ManagerCommented:
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
 
b0lsc0ttIT ManagerCommented:
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
 
robrodpAuthor Commented:
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
 
b0lsc0ttIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now