May 20, 2011 at 1:41 pm
Hello all,
I am kinda new to sql. I need help with creating a Stored PROCEDURE for validating the input Email.
I got all the conditions, but couldn't get the result. I mean if all the conditions are met it should print a message saying: valid Email. If any condition is not met it should print that particular reason.(that's why i put individual if conditions)
Also, how to create this stored PROCEDURE? Thanks in advance.
Here are my codes:
Create PROCEDURE VALIDATE_EMAIL
@email_address varchar(255)
AS
if @email_address is null return
DECLARE @email_address varchar(255),
@v1 varchar(25)
SET @email_address ='david_mathew@cddd.vvv'
set @v1 ='invalid email'
IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0 )
print ''
ELSE
print 'No Spaces Allowed'
IF
( LEFT(LTRIM(@email_address),1) <> '@' )
print ''
ELSE
print '@ can not be the first character of an email address'
IF
( LEFT(LTRIM(@email_address),1) <> '.' )
print ''
ELSE
print '. can not be the first character of an email address'
if( RIGHT(RTRIM(@email_address),1) <> '.' )
print ''
ELSE
print '. can not be the last character of an email address'
if( LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1 )
print ''
ELSE
print ' Add @'
If( CHARINDEX('.',REVERSE(RTRIM(LTRIM(@email_address)))) >= 3 )
print ''
ELSE
print '. Add domain name ex: .com'
if( (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0
AND CHARINDEX('@@',@email_address ) = 0
AND CHARINDEX('#',@email_address ) = 0
AND CHARINDEX('^',@email_address ) = 0
AND CHARINDEX('&',@email_address ) = 0
AND CHARINDEX('*',@email_address ) = 0
AND CHARINDEX('(',@email_address ) = 0
AND CHARINDEX(')',@email_address ) = 0
AND CHARINDEX('+',@email_address ) = 0
AND CHARINDEX('=',@email_address ) = 0)
)
print ''
ELSE
print 'invalid character'
Thank you again
May 20, 2011 at 3:29 pm
There are a lot of issues with this code. I don't think I would use sql as a way to validate an email address. This could probably be a UDF instead of a sproc since you really just want to evaluate something.
That aside let's look at your code.
What is the point of the print statement? If you want to make this a procedure the print is not going to do anything. If the print is there to help debug until you figure it out that's cool.
There are so many logic error in here it is hard to sort them all out so I just recreated this so you can see where you went wrong on things.
create function IsValidEmail
(
@email_address varchar(255)
) returns bit
as begin
declare @ErrMsg varchar(max) = ''
IF CHARINDEX(' ',LTRIM(RTRIM(@email_address))) > 0
set @ErrMsg = @ErrMsg + CHAR(13) + 'No Spaces Allowed'
IF LEFT(LTRIM(@email_address),1) = '@'
set @ErrMsg = @ErrMsg + CHAR(13) + '@ can not be the first character of an email address'
IF LEFT(LTRIM(@email_address),1) = '.'
set @ErrMsg = @ErrMsg + CHAR(13) + '. can not be the first character of an email address'
if RIGHT(RTRIM(@email_address),1) = '.'
set @ErrMsg = @ErrMsg + CHAR(13) + '. can not be the last character of an email address'
if LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) <> 1
set @ErrMsg = @ErrMsg + CHAR(13) + ' Add @'
If( CHARINDEX('.',REVERSE(RTRIM(LTRIM(@email_address)))) > 4 )
set @ErrMsg = @ErrMsg + CHAR(13) + '. Add domain name ex: .com'
if( (CHARINDEX('.@',@email_address ) > 0 OR CHARINDEX('..',@email_address ) > 0
OR CHARINDEX('@@',@email_address ) > 0
OR CHARINDEX('#',@email_address ) > 0
OR CHARINDEX('^',@email_address ) > 0
OR CHARINDEX('&',@email_address ) > 0
OR CHARINDEX('*',@email_address ) > 0
OR CHARINDEX('(',@email_address ) > 0
OR CHARINDEX(')',@email_address ) > 0
OR CHARINDEX('+',@email_address ) > 0
OR CHARINDEX('=',@email_address ) > 0)
)
set @ErrMsg = @ErrMsg + CHAR(13) + 'invalid character'
declare @Result bit
if @ErrMsg = ''
set @Result = 1 --it IS valid
else
set @result = 0
return @Result
end
go
select dbo.IsValidEmail('david_mathew@cddd.vvv')
That will "correct" what you have here but this is far from being a validation tool that is even close. For example what about the .name domains? This will also allow for things like a@a.a I would think about validating before it gets to sql or create a CLR function. The latter will give you power of regular expressions. SQL just isn't a great tool for string validation.
I didn't look too deep but this looks like a good article to get you started. The author showed his first attempt which used a dreadfully slow loop to look at each character then progressed to using a RegEx which is WAY better.
--edited to add link to the article i referenced...it is Friday and I am ready for a beer. :Whistling:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2011 at 3:33 pm
Hello Sean,
Thank you so much for you help.
Dave
May 20, 2011 at 3:34 pm
Let us know if you need some help getting it finished up. Glad I could help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2011 at 4:03 pm
I probably would have taken a different approach.. I would likely do it as a UDF.. But I wrote a quick sproc.. It is attached..
CEWII
May 20, 2011 at 4:39 pm
You may want to take a read through this, and all the technical documentation it links to http://haacked.com/archive/2007/08/21/i-knew-how-to-validate-an-email-address-until-i.aspx
The following characters are all valid in an email address: ! $ & * - = ^ ` | ~ # % ' + / ? _ { }
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 10:42 pm
Thank you , But I couldn't open your attachment.
May 25, 2011 at 8:38 am
Hm, had no problem myself.. Just take the .txt off the end and SSMS will see it..
CEWII
May 25, 2011 at 8:48 am
Thank you sir. Now I am able to download it. It gives me a error message:
Msg 156, Level 15, State 1, Procedure ValidateEmailAddress, Line 50
Incorrect syntax near the keyword 'PRINT'.
Thanks,
Dave
May 25, 2011 at 9:23 am
Oops..
Fix this line:
+ CASE WHEN LEN( @EmlDom ) < 4 THEN 'The email domain is too short'
With
+ CASE WHEN LEN( @EmlDom ) < 4 THEN 'The email domain is too short' ELSE '' END
bonehead on my part...
CEWII
May 25, 2011 at 9:47 am
Hello sir,
Thanks again. I was able to create this PROCEDURE. Really appreciate all your time and efforts.
It has one problem that: it is only printing the following message for :The email address contains too many @ characters, there may only be one, The email address contains invalid characters such as #, ^, &, *, (, ), +, =, @@,
for every error.
for ex: I put: dm@y.c' or dm@yahoo.c
exec dbo.ValidateEmailAddress 'dm@@y.c'
exec dbo.ValidateEmailAddress 'dm@yahoo' ---->this one gives no error message at all.
Also, I would like to print a message saying: valid email if the input email is valid one.
I really appreciate all you help. I am still in learning phase.
Dave
May 25, 2011 at 9:47 am
Hello sir,
Thanks again. I was able to create this PROCEDURE. Really appreciate all your time and efforts.
It has one problem that: it is only printing the following message for :The email address contains too many @ characters, there may only be one, The email address contains invalid characters such as #, ^, &, *, (, ), +, =, @@,
for every error.
for ex: I put: dm@y.c' or dm@yahoo.c
exec dbo.ValidateEmailAddress 'dm@@y.c'
exec dbo.ValidateEmailAddress 'dm@yahoo' ---->this one gives no error message at all.
Also, I would like to print a message saying: valid email if the input email is valid one.
I really appreciate all you help. I am still in learning phase.
Dave
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply