SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email validation question


Email validation question

Author
Message
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14898 Visits: 15980
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
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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
*/



sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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


Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15643 Visits: 14396
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
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
Before I get started, do you know if it's compatible with 2005?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15643 Visits: 14396
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
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15643 Visits: 14396
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"Wink]
    
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search