T-SQL:检查电子邮件格式 [英] T-SQL: checking for email format
问题描述
@email_address VARCHAR(200)
,我想检查 @email_address的价值
是电子邮件格式。任何人都有任何想法如何检查T-SQL格式? 非常感谢!
不同的错误和有效的邮箱地址。应该做这个工作。 (
CHARINDEX('',LTRIM(RTRIM(@email_address)))= 0
AND LEFT(LTRIM(@email_address),1)<"'@'
AND RIGHT(RTRIM(@email_address),1)&''''
AND CHARINDEX(' ,@ email_address,CHARINDEX('@',@ email_address)) - CHARINDEX('@',@ email_address)> 1
AND LEN(LTRIM(RTRIM(@email_address))) - LEN(REPLACE(LTRIM RTRIM(@email_address)),'@',''))= 1
AND CHARINDEX('。',REVERSE(LTRIM(RTRIM(@email_address))))> = 3
AND( CHARINDEX('。@',@ email_address)= 0 AND CHARINDEX('..',@ email_address)= 0)
)
打印'有效的电子邮件地址'
ELSE
打印'无效'
它检查这些条件:
- 没有嵌入空格
- '@'不能是电子邮件地址的第一个字符
- '。'不能是电子邮件地址的最后一个字符
- 必须有一个'。'
- 允许使用'@'标志
- 域名应以至少2个字符扩展名结尾。
- 不能有像'。@'和'..'的模式
I have this scenario where I need data integrity in the physical database. For example, I have a variable of @email_address VARCHAR(200)
and I want to check if the value of @email_address
is of email format. Anyone has any idea how to check format in T-SQL?
Many thanks!
I tested the following query with many different wrong and valid email addresses. It should do the job.
IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
print 'valid email address'
ELSE
print 'not valid'
It checks these conditions:
- No embedded spaces
- '@' can't be the first character of an email address
- '.' can't be the last character of an email address
- There must be a '.' somewhere after '@'
- the '@' sign is allowed
- Domain name should end with at least 2 character extension
- can't have patterns like '.@' and '..'
这篇关于T-SQL:检查电子邮件格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!