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.
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 EmailtableWhere -(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 dotsStill needs to be doneThanks |
|
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. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-17 : 20:33:58
|
Only dash or dot, @, 0-9, a-z characters onlypatindex('%[^-.@0-9]%', email_address) = 0for the not ..email_address like '..%' OR email_address lIKE '%..' OR email_address lIKE '%..%'can be justemail_address lIKE '%..%'at least 2 characters after dotis that the last dot?charindex('.',reverse(email_address)) >= 2so for valid oneswhere patindex('%[^-.@0-9]%', email_address) = 0and email_address like '%.%'and email_address like '%@%'and email_address not like '%..%'and email_address not like '%@@%'and charindex('.',reverse(email_address)) >= 2but 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. |
|
|
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 @myTableSelect * From @myTableWhere 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 |
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2004-08-18 : 09:45:26
|
Awesome thanks you all. Thanks nr! |
|
|
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 |
|
|
|
|
|
|
|