| Author |
Topic |
|
mgonda
Starting Member
29 Posts |
 Posted - 2012-01-27 : 13:31:31
|
| I am trying to clean up my database a little, and am looking at common misspellings and fixing them. I am using LIKE '%@%@%' to see if 2 @ signs are put into the field, which would make an invalid email address, but my goal is to now look to see if there is no @ sign, which also makes it an invalid email address. I don't know how to do even begin. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-27 : 13:32:46
|
| just do like WHERE CHARINDEX('@',EmailField) = 0------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-27 : 13:34:31
|
| You can use: NOT LIKE '%@%' |
|
|
|
mgonda
Starting Member
29 Posts |
Posted - 2012-01-27 : 13:34:36
|
| Thank you very much! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-27 : 13:37:00
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mgonda
Starting Member
29 Posts |
Posted - 2012-01-27 : 13:48:48
|
| When I did this, it was giving me the people that had no email address on file too, is there a way to exclude those? because I can't fix something that isn't there. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-27 : 13:50:21
|
quote:
Originally posted by mgonda
When I did this, it was giving me the people that had no email address on file too, is there a way to exclude those? because I can't fix something that isn't there.
no email address means blank values??WHERE CHARINDEX('@',EmailField) = 0AND EmailField > ''------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mgonda
Starting Member
29 Posts |
Posted - 2012-01-27 : 13:53:56
|
| Yeah, that's what it was. I was trying is not null. When I switched it to <>'' it worked. Thanks again. |
|
|
|
steve.caplin77
Starting Member
3 Posts |
Posted - 2012-01-27 : 14:11:10
|
| May be you missing some string % in email fields.. Check it again..unspammed |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-27 : 14:15:47
|
quote:
Originally posted by mgonda
Yeah, that's what it was. I was trying is not null. When I switched it to <>'' it worked. Thanks again.
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-27 : 14:26:30
|
| EmailField LIKE '%@%' AND EmailField NOT LIKE '%@%@%' should be fine. But a more sophisticated check would be prudent - something that checks that the TLD is actually valid, not rogue characters afte the "@" and so on - better still do an MX lookup to make sure the email's domain is reachable. Just checking for "@" is not taking care of the user's typo's, which will be commonplace! |
|
|
|
|
Check emails for missing @