Using LIKe - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using LIKe

Author

Topic

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-17 : 17:40:46

I am trying to retrieve email ids satisfying the following conditions. I definitely don’t want to use the cursors. Can we do it in single SQL STATEMENT?

-- Only dash or dot, @, 0-9, a-z characters only.
-- should contain at least one dot and one @.
-- It cannot have more than one dot consecutively or @.
-- It should have at least 2 characters after dot.

Here is what i come up with

Select customer_id,email_address from Emailtable

Where -

(email_address Not like '%.%' OR email_address Not Like '%@%') OR

-- 2.Must have atleast one Dot and one @

(email_address like '..%' OR email_address lIKE '%..' OR email_address lIKE '%..%') OR

-- 3.It cannot have more than one consecutive dot or @.

(email_address LIKE '%@@' OR email_address LIKE '@@%' OR email_address LIKE '%@@%') OR

(len( substring( email_address, charindex('.',email_address)+1,50)) < 2 )

-- 4.It should have at least 2 characters after dot

-- This doesn’t help if the address has more than 2 dots

Still needs to be done

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-17 : 20:48:43

Does this work??

Declare @myTable table (email nvarchar(100))

Insert Into @myTable Values ('Corey.Aldebol@a.com')
Insert Into @myTable Values ('Kevin.Black@microsoft.c')
Insert Into @myTable Values ('KellyJackson@.microsoft.com')
Insert Into @myTable Values ('Bill.Gates@microsoft.com')
Insert Into @myTable Values ('Charlie..Clements@microsoft.com')
Insert Into @myTable Values ('A,B@microsoft.com')

Select * From @myTable

Select * From @myTable
Where email not like '%[^a-z0-9A-Z.@]%'
and email like '_%@_%.__%'
and email not like '%..%'
and email not like '%@.%'
and email not like '%.@%'

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 20:49:09

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38724

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-17 : 20:50:06

Are email addresses stored in a table?

What you want to do?

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources