Email Procedures - 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

Email Procedures

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-03 : 18:56:14

For what it's worth, here are 3 procedures I wrote which helped me with the qualification and ordering of existing email lists.

The EmailDomain and EmailPrefix functions are pretty neat to order selected results:

Select Email from Mytable
ORDER BY dbo.EmailDomain(Email), dbo.EmailPrefix(Email)

Finding invalid emails is easy with
SELECT Email from MyTable
WHERE dbo.EmailIsValid(Email)=0

Any comments on improvements would be appreciated.

SamC

CREATE FUNCTION dbo.EmailIsValid (@Email varchar (100))
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

CREATE FUNCTION dbo.EmailDomain (@Email varchar (100))
RETURNS varchar (100)
AS
BEGIN
RETURN (SUBSTRING(@Email, charindex('@', @Email+'@')+1, LEN(@Email)))
END
Go

CREATE FUNCTION dbo.EmailPrefix (@Email varchar (100))
RETURNS varchar (100)
AS
BEGIN
RETURN (LEFT(@Email, charindex('@', @Email+'@')-1))
END
Go

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-06 : 22:41:18

I have written an email validation component which validates email on three levels.

1) Syntax
2) Domain validity check
3) SMTP validation

The proc uses a COM component available from Hexillion.com
and call it from SQL using sp_oacreate.

If you are interested in something like this check out http://www.hexillion.com/software/components/HexValidEmail/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources