Using LIKE

• Shas3

  Hall of Fame

  Points: 3671

  More actions

  August 17, 2004 at 8:35 am

  #109033

  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.

   

  Thanks

  Shas3

  Shas3

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736455

  More actions

  August 17, 2004 at 10:52 am

  #519398

  You might be able to, but it would be ugly and might cause performance issues. I'd create a couple views to filter things out.

  like create view myfirst as select * from emails where len( substring( email, charindex('.',email)+1,20) > 2 for the last one. However that doens't help with some emails, like steve.jones@sqlservercentral.co which would error out.

  I think you should tackle this with a stored procedure or some sort of staging table where you cleanse the data and search out anomolies.

• Shas3

  Hall of Fame

  Points: 3671

  More actions

  August 17, 2004 at 3:42 pm

  #519463

  Thanks Steve. That help little bit. 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

   

  Shas3

• Shas3

  Hall of Fame

  Points: 3671

  More actions

  August 18, 2004 at 8:03 am

  #519588

  Got It

  Select emails from EmailTable where

  Emais <> '' AND

  ( patindex('%[^-.@0-9a-zA-Z]%', emails) = 1

  OR emails NOT like '%.%'

  OR emails NOT like '%@%'

  OR emails like '%..%'

  OR emails like '%@@%'

  OR charindex('.',reverse(emails)) < 2 OR emails like '%@.%'

  OR emails like '%.@%')

   

   

  Shas3