Algorithm For Email

Question

Post reply

Algorithm For Email

TriggerMe

SSChampion

Points: 11752
More actions
June 11, 2008 at 11:14 pm

#186683

Does anyone know how to place an algorithm for an Email Column? The Datatype of this Column is VARCHAR(50)
I basically want to enforce the email format of for example joe.blogs@domain.com
Obviously to prevent users from entering whatever they like for an email address.
Is this possible via a Check Constraint? If so how?
Kindest Regards,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

>> to prevent users from entering whatever they like for an email address.

It is better to do this validation in the front end.

TriggerMe SSChampion Points: 11752 More actions · Answer 2

We refrain form placing such code within the Application. I need a solution that will be handled by the Database Engine.

Kindest Regards,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It is possible to do that as a check constraint, however be aware that email addresses are very complex to validate properly. See here for some details on what is and is not allowed as part of an email address.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

I'd say that's a pretty tall order unless you have a particular domain in mind that want's particular formatting rules. Then, you could use LIKE to build a formula. Other than that, as the link Gail provided shows, Email addresses do, in fact, allow pretty much anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Perhaps what you could do is just a simple check. Does the address have an '@' sign in? Does it have at least 1 '.' (though I seem to recall reading somewhere that there are 1 or 2 top level domains that allow email)

The only real way to validate that an email address is corrct is to send mail to the address and have some way of making the user prove they received it (confirmation codes or the like). Though that's not somethng you could do in SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

D1rtyD0g Ten Centuries Points: 1045 More actions · Answer 6

I have been using this constraint on one of my tables & no issues...

ALTER TABLE [dbo].[TableName]

WITH CHECK ADD

CONSTRAINT [EmailValidator]

CHECK

(

CHARINDEX(' ',LTRIM(RTRIM([ColumnName]))) = 0 -- No embedded spaces

AND LEFT(LTRIM([ColumnName]),1) <> '@' -- '@' can't be the first character of an email address

AND RIGHT(RTRIM([ColumnName]),1) <> '.' -- '.' can't be the last character of an email address

AND CHARINDEX('.',[ColumnName],CHARINDEX('@',[ColumnName])) - CHARINDEX('@',[ColumnName]) > 1 -- There must be a '.' after '@'

AND LEN(LTRIM(RTRIM([ColumnName]))) - LEN(REPLACE(LTRIM(RTRIM([ColumnName])),'@','')) = 1 -- Only one '@' sign is allowed

AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([ColumnName])))) >= 3 -- Domain name should end with at least 2 character extension

AND (CHARINDEX('.@',[ColumnName]) = 0 AND CHARINDEX('..',[ColumnName]) = 0) -- can't have patterns like '.@' and '..'

)

GO

If this ALTER statement fails, run the following...

SELECT *

FROM TableName

WHERE NOT

(

CHARINDEX(' ',LTRIM(RTRIM([ColumnName]))) = 0

AND LEFT(LTRIM([ColumnName]),1) <> '@'

AND RIGHT(RTRIM([ColumnName]),1) <> '.'

AND CHARINDEX('.',[ColumnName],CHARINDEX('@',[ColumnName])) - CHARINDEX('@',[Email]) > 1

AND LEN(LTRIM(RTRIM([ColumnName]))) - LEN(REPLACE(LTRIM(RTRIM([ColumnName])),'@','')) = 1

AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([ColumnName])))) >= 3

AND (CHARINDEX('.@',[ColumnName]) = 0 AND CHARINDEX('..',[ColumnName]) = 0)

)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Ummm... there's some responsibility required on the part of the user... you make them enter it twice and not allowed to proceed unless they match. If they get it wrong, it's their fault, not the system's. If they really don't want to give you a correct email, how are you going to stop them? I've been know to use jmoden@nospam.com or jmoden@youwish.com where people inappropriately require it. Even with double entry, you can't stop that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TriggerMe SSChampion Points: 11752 More actions · Answer 8

Tobie,

This works well. I will use it. It suits our requirements.

Thanks.

Kindest Regards,