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
 SQL Server Administration (2000)
 Using LIKE

Author  Topic 

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-17 : 18:03:38
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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-17 : 19:18:26
Seems like you've got it mostly sorted. What exactly do you want to know?

And don't repost.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-17 : 20:33:58
Only dash or dot, @, 0-9, a-z characters only
patindex('%[^-.@0-9]%', email_address) = 0

for the not ..
email_address like '..%' OR email_address lIKE '%..' OR email_address lIKE '%..%'
can be just
email_address lIKE '%..%'

at least 2 characters after dot
is that the last dot?

charindex('.',reverse(email_address)) >= 2

so for valid ones

where patindex('%[^-.@0-9]%', email_address) = 0
and email_address like '%.%'
and email_address like '%@%'
and email_address not like '%..%'
and email_address not like '%@@%'
and charindex('.',reverse(email_address)) >= 2

but what about (not sure about these)
no @ after last dot charindex('@',reverse(email_address)) < charindex('.',reverse(email_address))
@ or dot can't be first char not like '[.@]%'
dot and @ can't be consecutive not like '%[.@][.@]%'
one and only one @ allowed len(replace(email_address,'@','') + 1 = len(email_address)


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-17 : 20:51:15
From the other post....


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 '%.@%'
and email not like '%@@%'



Corey
Go to Top of Page

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-18 : 09:45:26
Awesome thanks you all. Thanks nr!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-18 : 14:00:14
Wouldn't a Regular Expression, Client- or Application- Side, but much more all-encompassing for this job?

<rant>SQL Server needs RegEx</RANT.RANT.RANT>

Kristen
Go to Top of Page
   

- Advertisement -