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.


Roberto Madro R.Programmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
Roberto Madro R.Programmer AnalystAuthor Commented:
Will Trim(NVL(fieldname ..... work together? which one oracle will process first, or does it matter.
slightwv (䄆 Netminder) Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roberto Madro R.Programmer AnalystAuthor Commented:
So something like this ;
NVL(TRIM(Field3,'noEmail') Email,

slightwv (䄆 Netminder) Commented:
Looks good to me.  Well, once you close the TRIM function.   NVL(TRIM(Field3),'noEmail') Email,

Set up a simple test and try things.

with mytest as(
select '' email_address from dual
union all
select '        q' from dual
union all
select '        q         ' from dual
union all
select '                  ' from dual
select nvl(trim(email_address),'no email') from mytest;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roberto Madro R.Programmer AnalystAuthor Commented:
Worked like a charm on 99.9% of the records, there's that 0.1% that has emails as follows :           ~           `

The spaces in the examples cited are between characters in that field, does that mean Regex or nothing?
slightwv (䄆 Netminder) Commented:
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.
Roberto Madro R.Programmer AnalystAuthor Commented:
As usual slightwv, many thanks for your input and overall thoughts. some data cleansing on our part is in order.

Many Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.