Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email validation question Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 298, Visits: 1,186
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

Post #1444465
Posted Friday, April 19, 2013 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 5,074, Visits: 8,909
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
Post #1444468
Posted Friday, April 19, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 298, Visits: 1,186
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
*/


Post #1444474
Posted Sunday, April 21, 2013 9:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 298, Visits: 1,186
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

Post #1444844
Posted Tuesday, April 23, 2013 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1445399
Posted Tuesday, April 23, 2013 8:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 298, Visits: 1,186
Before I get started, do you know if it's compatible with 2005?
Post #1445448
Posted Tuesday, April 23, 2013 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1445558
Posted Tuesday, April 23, 2013 11:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 298, Visits: 1,186
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.
Post #1445600
Posted Tuesday, April 23, 2013 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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:

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

}
;


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1445627
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse