Improve this Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Improve this Function

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-30 : 18:26:14

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

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-30 : 18:30:32

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');
}

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-06 : 15:02:23

[code]
/^([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
[/code]

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 ..."

Kristen
Test

22859 Posts

Posted - 2006-01-06 : 15:39:53

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-06 : 15:53:12

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-06 : 15:59:14

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 ..."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-06 : 16:02:24

you seem overworked...

kaiden getting her teeth?

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-06 : 16:20:56

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 ..."

Kristen
Test

22859 Posts

Posted - 2006-01-06 : 21:05:21

"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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources