如何验证存储过程中的电子邮件 [英] how to validate the email in stored procedure

问题描述

请一些人告诉如何在存储过程中验证emailid。

请一些人告诉......

pls some one tell that how to validate emailid in stored procedure.
pls some one tell......

推荐答案

创建以下功能：

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

如果此函数返回1，则电子邮件ID有效，否则无效。

If this function returns 1 then Email id is valid otherwise it is invalid.

请参阅： MS SQL Server中的正则表达式2005/2008 [ ^ ]

和此： http： //msdn.microsoft.com/en-us/magazine/cc163473.aspx [ ^ ]



更多关于验证电子邮件的模式： http://www.regular-expressions.info/examples.html [ ^ ]

See this: Regular Expressions in MS SQL Server 2005/2008[^]
and this: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx[^]

More about patterns to verify email here: http://www.regular-expressions.info/examples.html[^]

这篇关于如何验证存储过程中的电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！