email validation transact sql

[dgaylor]

I need to validate email addresses that are already stored in my database using transact sql. I know how to use regular expressions to validate the structure of the email address using VB Script, but not how to do it in transact sql. Can anyone help me? Thanks.

 

[essa2000]

Dear ;

I have also the script in VB that validate Email and URL. You can covert the same logic in Transact SQL. I'll work on it and get back to you.

Regards,
Essa Mughal

 

[tpasters]

Did you ever get an answer on this? I also need to validate an email addredd with T-SQL

 

[dgaylor]

Never got an answer but for my purpose I just wrote the following code. Thanks.

update email_table
set good_email = 'Y'
where
(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)))) in (3,4)) AND
(CHARINDEX('.@', email_address) = 0 AND CHARINDEX('..', email_address) = 0 )