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 |
|
dgaylor
Yak Posting Veteran
54 Posts |
 Posted - 2002-06-25 : 12:17:57
|
| I have a script in vb that uses regular expressions to validate that email address are correct, but have no idea how to do it in transact sql. Any hints? Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 12:21:30
|
| Look in Books Online under "LIKE", "Charindex" and "PatIndex". SQL's pattern matching ability is limited though, nothing like advanced regular expressions, but it should be enough to validate an email address. |
 |
|
|
dgaylor
Yak Posting Veteran
54 Posts |
Posted - 2002-06-25 : 12:31:19
|
| Thanks. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-25 : 13:19:16
|
| Actually it's better to do email validation in the front-end. But well, sometimes we do need to validate stuff on the backend. Here's an example I just came up with. It's still not perfect and there could be a better way out. Lemme know how it works for you. I'll put it up on my site tomorrow.CREATE TABLE email (email varchar(50))ALTER TABLE email ADD CONSTRAINT EmailValidator CHECK ( (CHARINDEX(' ', LTRIM(RTRIM(email))) = 0) AND (LEFT(LTRIM(email), 1) <> '@') AND (RIGHT(RTRIM(email), 1) <> '.') AND (CHARINDEX('.', email, CHARINDEX('@', email)) - CHARINDEX('@', email) > 1) AND (LEN(LTRIM(RTRIM(email))) - LEN(REPLACE(LTRIM(RTRIM(email)), '@', '')) = 1) AND (CHARINDEX('.', REVERSE(LTRIM(RTRIM(email)))) >= 3) AND (CHARINDEX('.@', email) = 0 AND CHARINDEX('..', email) = 0 ))--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-25 : 13:20:41
|
| ...additionally, you need to look for invalid characters (those that are not allowed in an email address) as well!--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
dgaylor
Yak Posting Veteran
54 Posts |
Posted - 2002-06-25 : 14:31:29
|
| thanks for the great start - i appreciate it. |
|
|
|
|
|
|
|
|
Email Validation Syntax