Widcards?
-
Hi all,
I am trying to do some basic integrity checking on incoming email addresses. I want to add a Derived Column to state whether or not the email field is valid in a record.
To be valid, the email address needs to exist, and be in the xxx@xxx.xxx format.
Currently I am using
!ISNULL(Email) && Email != "" && FINDSTRING(Email,"@",1) != 0 && FINDSTRING(Email,".",1) != 0 ? "Passed" : "Failed"
but have since realised that this obviously allows such addresses as Madison.Jackson@highmail to slip through , marked as Passed.
What I really need to do is specify that the email format must be x@x.x.
Is this possible?
Thanks,
Paul
-
You should be able to use PATINDEX('%@%.%', EMAIL) IS NOT NULL to make sure it matches a valid email pattern.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
PATINDEX is not a valid function in SSIS expressions.
Maybe adding an additional condition would help:
&& FINDSTRING(Email,"@",1) < (LEN(Email)) - FINDSTRING(REVERSE(Email),".",1))
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Paul_Harvey (2/4/2010)
Hi all,I am trying to do some basic integrity checking on incoming email addresses. I want to add a Derived Column to state whether or not the email field is valid in a record.
To be valid, the email address needs to exist, and be in the xxx@xxx.xxx format.
Currently I am using
!ISNULL(Email) && Email != "" && FINDSTRING(Email,"@",1) != 0 && FINDSTRING(Email,".",1) != 0 ? "Passed" : "Failed"
but have since realised that this obviously allows such addresses as Madison.Jackson@highmail to slip through , marked as Passed.
What I really need to do is specify that the email format must be x@x.x.
Is this possible?
Thanks,
Paul
A better approach would be to use regular expression to detect correct email addresses. You have to implement an SSIS script component. You may check this script for reference.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Phils method seems to be working fine. So far I haven't seen any invalid email addresses slipping though the net!
Thanks Phil!
Paul
-
Thanks for the feedback, glad to have helped.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
sorry, are you saying that you dont want to let email addresses through that follow the format 'text.text@gmail.com' ?
because thats the format of my address
-
Not at all. The issue I had was that it was allowing addresses though WITHOUT the .com on the end. ie text.text@gmail
Phils amendment to my expression now means that the dot must come somewhere AFTER the @ - which has prevented these errors.
-
Paul_Harvey (2/5/2010)
Not at all. The issue I had was that it was allowing addresses though WITHOUT the .com on the end. ie text.text@gmailPhils amendment to my expression now means that the dot must come somewhere AFTER the @ - which has prevented these errors.
aah sorry.
i noticed the following line 'Madison.Jackson@highmail' and thought it was the dot in between the name that you had a problem with 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply