Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
pls some one tell that how to validate emailid in stored procedure.
pls some one tell......
Posted 14-Feb-13 3:12am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

create following function :
 
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
 
 
If this function returns 1 then Email id is valid otherwise it is invalid.
  Permalink  
Comments
Maciej Los at 14-Feb-13 8:34am
   
Wow, clear SQL...
Interesting function, +5!
;)
Bhushan Shah1988 at 14-Feb-13 8:36am
   
Thanks Maciej Los :D
Asim Mahmood at 14-Feb-13 8:34am
   
Why you need to do it at DB level you RegularExpression at code end and validate that.
chander_rani at 14-Feb-13 8:48am
   
but i need it in stored procedure. pls help me to do validation in stored procedure
Bhushan Shah1988 at 15-Feb-13 2:40am
   
you can use above function in stored procedure.
 
try dis :
 
select IsValidEmail(bhushanshah21@gmail.com)
 
and
 
select IsValidEmail(bhushan shah21@gmail.com)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Afzaal Ahmad Zeeshan 256
1 OriginalGriff 251
2 BillWoodruff 240
3 Maciej Los 230
4 Sergey Alexandrovich Kryukov 195
0 OriginalGriff 6,419
1 Sergey Alexandrovich Kryukov 6,008
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 4,070


Advertise | Privacy | Mobile
Web01 | 2.8.1411019.1 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100