Anyone have a well-formed email checker? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Anyone have a well-formed email checker?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-05 : 14:13:32

I'm looking for a UDF that will do a basic validation of an email address; one and only one @, alpha-only for TLD, etc. It'd be easy enough to write, but I'm lazy. If someone's got one, would you post it to the script library?

Cheers
-b

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-05 : 18:24:24

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19434[/url]

I made a small revision, here it the latest...

Sam

DROP FUNCTION dbo.EmailIsValid
GO
CREATE FUNCTION dbo.EmailIsValid (@Email varchar (100))
/*************************************************************************************************

Purpose: Return TRUE (BIT=1) if email is syntatically valid
Used to locate invalid email: SELECT Email FROM Table WHERE NOT dbo.EmailIsValid(Email)

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

SET @Email = LTRIM(RTRIM(@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
GRANT EXECUTE ON dbo.EmailIsValid TO Public
GO

DROP FUNCTION dbo.EmailDomain
GO
CREATE FUNCTION dbo.EmailDomain (@Email varchar (100))
/*************************************************************************************************

Purpose: Return the Email address DOMAIN ( '@domain.xxx' ) as VARCHAR
Complimentary function to dbo.EmailPrefix
Use: ORDER BY dbo.EmailDomain(Email), dbo.EmailPrefix(Email)

*************************************************************************************************/
RETURNS varchar (100)
AS
BEGIN
RETURN (SUBSTRING(@Email, charindex('@', @Email+'@')+1, LEN(@Email)))
END
Go
GRANT EXECUTE ON dbo.EmailDomain TO Public
GO

DROP FUNCTION dbo.EmailPrefix
GO
CREATE FUNCTION dbo.EmailPrefix (@Email varchar (100))
/*************************************************************************************************

Purpose: Return the Email prefix ( 'myname' ) from myname@domain.xxx
Complimentary function to dbo.EmailDomain
Use: ORDER BY dbo.EmailDomain(Email), dbo.EmailPrefix(Email)

*************************************************************************************************/
RETURNS varchar (100)
AS
BEGIN
RETURN (LEFT(@Email, charindex('@', @Email+'@')-1))
END
GO
GRANT EXECUTE ON dbo.EmailPrefix TO Public
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources