Link to home
Start Free TrialLog in
Avatar of Roberto Madro R.
Roberto Madro R.Flag for United States of America

asked on

Select the customer's email from a corrupted email field.

I'm looking for a clean cut way to select a customer's email from an email field, the problem is, the field gets mistreated by the users and I'll end up with missing (null) values, leading spaces and other characters, if the problem was contained to missing value only, the NVL function can take care of that, but how would you handle leading spaces or characters in such field.
Examples:

(james@abc.com)
(    james@abc.com)
(xyzJames@abc.com)
(xyz@james@abc.....)

Thx
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

trim will trim whitespace from both sides of a field:  select trim(email_address) from some_table;

What do you mean by "other characters"?

You can do a regexp_substr if you can define what you want as a "valid" email address.  A regular expression to get ALL valid email addresses gets quite complex from the one's I've seen on the web.
Avatar of Roberto Madro R.

ASKER

Will Trim(NVL(fieldname ..... work together? which one oracle will process first, or does it matter.
Oracle follows operator precedence so when nesting functions, it goes inside out.

I would go with NVL(TRIM( and yes, you can nest functions.

The reason NVL is out the outside is for when the email address field is all spaces.  TRIM will make it NULL so NVL can catch it.  If you flip them TRIM(NVL(:  Spaces isn't null so it will then pass it to TRIM which makes it null.
So something like this ;
Select
Field1,
Field2,
NVL(TRIM(Field3,'noEmail') Email,
Field4,

..
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked like a charm on 99.9% of the records, there's that 0.1% that has emails as follows :

james@abc.com           ~
james@abc.com           `

The spaces in the examples cited are between characters in that field, does that mean Regex or nothing?
As I mentioned above:  It all depends on how you want to define a "valid" email address.  We can't do that for you.

Once you get it defined, then we can probably help.
As usual slightwv, many thanks for your input and overall thoughts. some data cleansing on our part is in order.

Many Thanks.