April 19, 2013 at 8:44 am
Hi,
A stored procedure calls on this UDF. I have to update it to exclude email addresses with '.@' or '@.' in them.
GO
/****** Object: UserDefinedFunction [dbo].[CHKEmail] Script Date: 04/19/2013 09:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- checks for a vaild email
-- returns 0 for valid addresses
-- returns 101 if address is less than 5 chars
-- returns 102 if address has less or more than 1 "@"
-- returns 103 if address has no "."
-- returns 104 if address has more than 3 chars after last "." but more than 1, and doesn't contain a valid Domain name
-- returns 105 if address has "_" after "@"
-- returns 106 if address contains invalid chars
--ALTER FUNCTION [dbo].[CHKEmail_test] (@email VARCHAR(50))
ALTER FUNCTION [dbo].[CHKEmail] (@email VARCHAR(50))
RETURNS INT AS
BEGIN
-- Remove invalid characters from e-mail address
set @email = isnull(@email,'')
IF @email > ''
BEGIN
DECLARE @i INT
DECLARE @atCnt INT
DECLARE @chkEmail INT
DECLARE @count INT
DECLARE @strcount int
DECLARE @strFind char(10)
DECLARE @strFindnext char(10)
DECLARE @strDomain char(10)-- Check domain names
DECLARE @maxemail int
SET @chkEmail = 0
-- Check length more than 5 chars ([Email address protected] should be
--shortest address)
If LEN(@email) < 5
BEGIN
SET @chkEmail = 101
GOTO RR
END
-- Check email address hat at least one "@"
SET @strCount=0
SET @Count =1
Start1: --Label
SELECT @strFind=SUBSTRING(@email,@Count,1)
IF @strFind='@'
BEGIN
SET @strCount=@strCount+1
END
SET @count=@Count+1
IF @Count <= len(@email)
BEGIN
Goto Start1
END
IF @strcount <> 1
BEGIN
SET @chkEmail=102
GOTO RR
END
-- Check e-mail address has at least one "."
SET @strCount=0
SET @Count =1
Start2: --Label
SELECT @strFind=SUBSTRING(@email,@Count,1)
IF @strFind='.'
BEGIN
SET @strCount=@strCount+1
END
SET @count=@Count+1
IF @Count <= len(@email)
BEGIN
Goto Start2
END
IF @strcount < 1
BEGIN
SET @chkEmail=103
GOTO RR
END
-- Check e-mail address has no more than 3 chars after the last ".", but
--more than 1
SET @strCount=0
SET @Count = len(@email)
SET @maxemail = len(@email)
Start3: --Label
SELECT @strFind=SUBSTRING(@email,@Count,1)
IF @strFind='.'
BEGIN
Set @strcount = @maxemail - @count
SET @strDomain = SUBSTRING(@email,@Count+1,@maxemail)
set @count = 0
END
SET @count=@Count-1
IF @Count > 0
BEGIN
Goto Start3
END
IF @strcount = 3
and @strDomain not in ('biz','cat','com','int','net','org','pro','tel','xxx','edu','gov','mil')
BEGIN
SET @chkEmail = 104
GOTO RR
END
IF @strcount > 3
and @strDomain not in ('aero','asia','coop','info','jobs','mobi','museum','name','travel')
BEGIN
SET @chkEmail = 104
GOTO RR
END
IF @strcount < 2
BEGIN
SET @chkEmail = 104
GOTO RR
END
-- Check e-mail address has no "_" after "@".
SET @strCount=0
SET @Count=1
Start4: --Label
SELECT @strFind=SUBSTRING(@email,@Count,1)
IF @strFind='@'
BEGIN
SELECT @StrFindnext=SUBSTRING(@email,@Count + 1,1)
IF @StrFindnext='_'
BEGIN
SET @chkEmail = 105
GOTO RR
END
END
SET @count=@Count+1
IF @Count <= len(@email)
BEGIN
Goto Start4
END
-- Check each char for validity.
SET @strCount=0
SET @Count =1
Start5: --Label
SELECT @strFind=SUBSTRING(@email,@Count,1)
IF @strFind IN
('~','`','!','#','$','%','^','&','*','(',')','=','+','\','|',']','[','}','{'
,'<','>','?','/','"',':',' ',';')
BEGIN
SET @chkEmail = 106
GOTO RR
END
SET @count=@Count+1
IF @Count <= len(@email)
BEGIN
Goto Start5
END
END
RR:
RETURN @ChkEmail
END
/*
SELECT dbo.CHKEmail('put email here') AS INVALID
*/
I tried altering this chunk to include those, but that doesn't work. Can anyone explain why? Or, I guess, is there a better solution out there?
IF @strFind IN
('~','`','!','#','$','%','^','&','*','(',')','=','+','\','|',']','[','}','{'
,'<','>','?','/','"',':',' ',';', '.@', '@.')
Thanks
April 19, 2013 at 8:55 am
You're setting the value of @strFind to a single character, yet you're then testing to see whether that value is in a list that includes strings of more than one character.
John
April 19, 2013 at 9:09 am
Hot damn. Thank you. Got it working by adding this section:
SET @strCount=0
SET @Count =1
Start6: --Label
SELECT @strFind=SUBSTRING(@email,@Count,2)
IF @strFind LIKE '%.@%'
OR @strFind LIKE '%@.%'
BEGIN
SET @chkEmail = 106
GOTO RR
END
SET @count=@Count+1
IF @Count <= len(@email)
BEGIN
Goto Start6
END
END
RR:
RETURN @ChkEmail
END
/*
SELECT dbo.CHKEmail('put email here') AS INVALID
*/
April 21, 2013 at 9:52 pm
If anyone is interested, I wrote a pretty useful email validation case statement. I know that I probably missed some domains, and I didn't touch two character country domains, but it's a good start, I think. I would appreciate feedback, since I'm not great at (especially) regex.
Thanks
(edit: it seems that apostrophes are allowed before the @)
with emailcheck as (
select @email as [Email],
case
when @email not like '[A-Za-z0-9_-.'']%@[A-Za-z0-9_-]%.[A-Za-z]%[A-Za-z]' then 'Invalid Format'
when len(@email) < 5 or @email is null then 'Invalid Length'
when charindex('@', @email) is null then 'Invalid Format - No @'
when charindex('.', @email) is null then 'Invalid Format - No .'
when charindex('_', @email) > charindex('@', @email) then 'Invalid _ After @'
when patindex ('%[ &,":;!+=\/()<>?]%',@email) > 0 then 'Invalid Chars'
when @email like '%.@%' or @email like '%@.%' then 'Invalid @ Seq'
when @email like '%@%@%' then 'Multiple @'
when @email like '%..%' then 'Invalid ..'
when len(substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))) = 3
and substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))
not in ('biz','cat','com','int','net','org','pro','tel','xxx','edu','gov','mil') then 'Invalid Domain 3 Chars'
when len(substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))) > 3
and substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))
not in ('aero','asia','coop','info','jobs','mobi','museum','name','travel') then 'Invalid Domain Over 3 Chars'
else 'Valid' end as [Dispo]
from @table
)
select email, dispo
from emailcheck
order by dispo
April 23, 2013 at 7:02 am
A SQLCLR object might be a better option here: http://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2013 at 8:07 am
Before I get started, do you know if it's compatible with 2005?
April 23, 2013 at 10:16 am
Should be. All the classes used in the code in the thread I linked to are in the .NET 2.0 Framework which is used by both the SQL 2005 and SQL 2008 R2 SQLCLR.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2013 at 11:29 am
When I click on any of the files to open them, they default to Visual Studio, which gives me an error saying it doesn't support files of this type. When I try to open them with SSMS, Project/Solution doesn't see any files in the folder, and File just opens the .sln file like a SQL syntax file.
I'm probably missing entire steps, since I've never done anything like this before.
April 23, 2013 at 12:22 pm
I think if you install a copy of SQL Server Data Tools on top of either VS 2010 or VS 2012 you now get the SQLCLR project types for free and you can target any version of SQL Server from the SQLCLR project type.
This is the source code for the UDF on the other thread I would recommend you start with:
[font="Courier New"]using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Net.Mail;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean IsValidEmailSystemNet(SqlString email)
{
try
{
System.Net.Mail.MailAddress ma = new System.Net.Mail.MailAddress(email.ToString());
return (SqlBoolean)true;
}
catch
{
return (SqlBoolean)false;
}
}
[SqlFunction(FillRowMethodName = "EmailIsValidSystemNet",
TableDefinition = "EmailIsValid bit")]
public static IEnumerable EmailValiditySystemNet(SqlString emailAddress)
{
ArrayList EmailCollection = new ArrayList();
EmailCollection.Add(emailAddress);
return EmailCollection;
}
//FillRow method. The method name has been specified above as
//a SqlFunction attribute property
public static void EmailIsValidSystemNet(object emailAddress,
out SqlBoolean emailIsValid)
{
try
{
MailAddress ma = new MailAddress(emailAddress.ToString());
emailIsValid = (SqlBoolean)true;
}
catch
{
emailIsValid = (SqlBoolean)false;
}
}
};
[/font]
And here is how you would call it:
SELECT t.EmailAddress,
x.EmailIsValid
FROM SomeTable t
CROSS APPLY dbo.EmailValiditySystemNet(t.EmailAddress) x;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply