Transact SQL – Validate Email Address

Background

Needing to validate an email address that is being passed to a script.

Sample

SQL


set nocount on
go

set XACT_ABORT on
go

/*
    Declare Table
*/
declare @tblPerson TABLE
(
      [id]	     smallint not null 
                     identity(1,1)

    , [emailAddress] nvarchar(300) null

    , [valid]        bit null default 0
)


/*
    Add Email Address
*/
insert into @tblPerson 
(
    [emailAddress]
)
values
  ('Joe')
, ('Jackie@jj.com')
, ('Sammie.Hagger@jj.com')
, ('Susan@jj')
, ('Paul.Young@yahoo.commmaa')
, ('Tina.Turner.Yahoo.c')
, ('Phil.Collins@yahoo')


/*
    Mark Records with Valid Email Address
*/
update tblP

set    [valid] = 1

from   @tblPerson tblP

where  
        (

                -- Domain Name is two characters
            ( 
                [emailAddress] 
                    like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]'
            )

                -- Domain Name is three characters
            or 
            ( 
                [emailAddress] 
                    like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z][a-z]'
            )
        
        )


/*
    List Records
*/
select *

from   @tblPerson tblP

 

Output

emailAddress.sample.01.20190914.0429AM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s