SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Improve this Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3467 Posts

Posted - 11/30/2005 :  18:26:14  Show Profile  Reply with Quote
I wrote the following function a few years ago - before I learned about SQL's PATINDEX function. It might be possible to check for a valid email address syntax with a single PATINDEX string which could replace the entire body of hte function below.

Is anyone is interested in taking a crack at it?

Signed... lazy Sam

CREATE FUNCTION dbo.EmailIsValid (@Email varchar (100))
/*
 RETURN 1 if @Email contains a valid email address syntax, ELSE RETURN 0
*/

RETURNS BIT
AS
BEGIN
DECLARE @atpos int, @dotpos int

SET @Email = LTRIM(RTRIM(IsNull(@Email, ''))) -- remove leading and trailing blanks

IF LEN(@Email) = 0 RETURN(0) -- nothing to validate

SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @

IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing

IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal

IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal

SET @dotpos =  CHARINDEX('.',REVERSE(@Email)) -- location (from rear) of last dot

IF (@dotpos < 3) or (@dotpos > 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @

RETURN(1) -- Whew !!

END
Go

Edited by - SamC on 11/30/2005 18:31:46

SamC
White Water Yakist

USA
3467 Posts

Posted - 11/30/2005 :  18:30:32  Show Profile  Reply with Quote
Here's a JavaScript that uses a regular expression, similar to PATINDEX supported expressions, to validate an email.

function checkMail()
{
	var x = document.forms[0].email.value;
	var filter  = /^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/;
	if (filter.test(x)) alert('YES! Correct email address');
	else alert('NO! Incorrect email address');
}
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/06/2006 :  15:02:23  Show Profile  Visit Seventhnight's Homepage  Reply with Quote

/^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/

Select @isValid = case when
email like '%[a-zA-Z0-9_.-]%@%[a-zA-Z0-9_.-]%.[a-zA-Z0-9_.-][a-zA-Z0-9_.-][a-zA-Z0-9_.-]'
and email not like '%[^a-zA-Z0-9_.-@]%'
and email not like '%@%@%'
and email not like '%..%' then 1 else 0 end

return @isValid



maybe??

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/06/2006 :  15:39:53  Show Profile  Reply with Quote
We put a RegEx in the HTML page (as JavaScript) so that duff Email addresses are few and far between. Then we do an MX on the Email address in the Application Layer. And then <g> we pass the Email address to SQL ...

.. for those that we "import" from elsewhere we use a COM object from SQL Server to VBScript to get a decent RegEx, and then compare the EMail addresses en-masse to a suitable RegEx.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/06/2006 :  15:53:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
i'd add another [a-zA-Z0-9_.-] at the end there corey.
you know the new .info domains

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/06/2006 :  15:59:14  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
the problem is that would force 4 characters... which would exclude .com etc...

oh i dont know...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/06/2006 :  16:02:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
you seem overworked...
kaiden getting her teeth?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/06/2006 :  16:20:56  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
shes got about 5 teeth... but she's not bad.

mostly overworked... migrating all of our production apps and servers to another location...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/06/2006 :  21:05:21  Show Profile  Reply with Quote
"you know the new .info domains"

Isn't ".museum" valid these days?

I wouldn't over-egg the regex. We find all sorts of actually-valid Email addresses that most RegEx's designed for the job reject. The RFC allows for extremely broad interpretation

But it does need a full blown RegEx tool to do the job - so either application layer, or COM object from SQL Server (or a .NET module in SQL2k5 I suppose)

email like '%[a-zA-Z0-9_.-]%@%[a-zA-Z0-9_.-]%.[a-zA-Z0-9_.-][a-zA-Z0-9_.-][a-zA-Z0-9_.-]'

That would let through domains ending in ....! and would not let through .CO.UK I think ... let alone the longer TLDs

and email not like '%[^a-zA-Z0-9_.-@]%'

Apostrophe and all sorts are permitted before the "@" sign I believe ...

and email not like '%@%@%'

That makes sense

and email not like '%..%' then 1 else 0 end

I'm not even sure that that is disallowed by the RFC (especially BEFORE the "@" where pretty much "anything goes" - including, God Forbid, spaces although in all the Email addresses our clients customers have registered with I've never seen one)

One approach might be to split off the bit after the "@" and check that more rigorously, as that does have a restricted permitted-character-set.

Sam: I've got some code lying around for a COM object RegEx check from SQL Server if you need it

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000