Validating @ , and length of an email

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Validating @ , and length of an email

Author

Topic

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-23 : 09:55:52

Im trying to perform an insert but with only valid emails that meet the following criteria:

Valid email : not Null, length > 6 with @ character

I know the is NOT NULL, but the other two im a bit confused on. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 10:00:53

You can very well validate this in your Front End application if you use
Use Len, InStr functions (VB6)

In SQL,

where email is not Null and len(email)>6 and charindex('@',email)>0

Madhivanan

Failing to plan is Planning to fail

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-23 : 10:09:40

Thanks but i need the SQL validation version.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 10:12:55

Then, where do you get data?

Madhivanan

Failing to plan is Planning to fail

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-23 : 10:15:06

From another table, but I don't want to insert invalid emails.

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-23 : 10:34:14

I need it to looking something like this:

INSERT INTO [Development].[dbo].[Exceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice])
SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice]
FROM [USCondex_Development].[dbo].[Table1]
WHERE advertiseremail is NULL
or adPrintID is NULL
or firstinsertdate is NOT NULL
or propertystreetaddress is NOT NULL
or propertyprice < 100

But I don't want the OR clauses in there, I just want multiple WHERE clauses, but that doesnt seem to work. I basically want to insert all values from multiple columns from the same table.

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-23 : 10:39:04

I'm sorry I posted my last reply under the wrong topic. Please disregard.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 01:33:43

quote:
Originally posted by gemispence

From another table, but I don't want to insert invalid emails.

Insert into table1(columns)
select columns from table2
where email is not Null and len(email)>6 and charindex('@',email)>0

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 01:47:16

"I don't want to insert invalid emails"

You need to do a lot more checking than len > 6 and contains "@" sign - as per my post to your other topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62258

I have done it using full regular expressions (using a COM object executed directly from SQL Server), but the best way IME is to use an MX lookup (which is easier from your application, than from SQL Server)

Kristen

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-24 : 04:25:31

We're getting a text file from a content partner and they will clean up the file somewhat, so we were really only asked to validate length, empties, and @ symbol. Thanks everyone for your help. I did finally use:

where email is not Null and len(email)>6 and charindex('@',email)>0

:)

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 12:42:52

Well, if they have allowed email addresses of less than 6 characters, or NOT containing "@", then it seems to me to be a fair bet there there is all sorts of other rubbish email addresses in there ... so they will cause problems when you try to send them.

You must as well allow everything through and deal with them along with all the other bounced emails you will get!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources