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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-08-27 : 18:42:09
|
| I have a column of user email addresses. Many are invalid.Is there a public domain SQL procedure out there that will vaildate the syntax of an email address?SamC |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-08-28 : 09:11:12
|
| doubt it.....but a forum search on 'email validate' will turn up several useful links.also....i believe there is a way to validate e-mail endings...ie the "@hotmail.com" bit...using some form of ping statement/or address lookup feature....say it mentioned somewhere recently...either here or on ZDnet....might be a good idea to build a 'valid e-mail suffix list'....and only actually validate an e-mail address if the suffix is new/unknown....might be a help re performance.... |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-28 : 11:21:30
|
| I've seen several methods to validate email - - syntax - reverse dns on domain - telnet to SMTP server to verify the emailI've done syntax in ASP.NET, reverse dns in ASP, one of these days I might try the 3rd option. If anyone has experience with a 3rd party library - please let me know.Back to syntax - on SQL. Someone must've done this before. I can imagine a 'simple' syntax checker - verify the '@' and one '.'CREATE FUNCTION dbo.EmailSyntax (@Email varchar (50))RETURNS BITASBEGINRETURN (CASE WHEN (charindex('@',@Email)>1 and charindex('@',reverse(@Email))>1 and charindex('.',reverse(@Email))>2) THEN 1 ELSE 0 END )ENDGoIf there's a better / more robust solution available that checks valid characters or positioning, my thanks in advance.SamC |
|
|
|
|
|
|
validating Email address in Column